Changing formula reference

[guil]

Hello !
I have a question that looks simple in my mind, and I hope Excel has the ability to perform this, as it would save me significant time.
I will give a simple example:

Let's say I have a formula =C1-B1
For example C represents march and B represents february.
Next month, my monthly variance will become D1-C1 (april vs march).

I want to be able to have the reference in 2 cells and by changing the value in these 2 cells the formula would automatically.

I would have in one cell : C
and in another cell : B

I would want to change the first cell to D and the 2nd to C, so that my formula updates.
I know I could use the find & replace function but I am looking for an alternative solution to that which will save me a lot of time.

Thank you very much !!

[Canapone]

Hi,

a first example could be:

D2 houses C, D3 houses B

=INDIRECT(D2&1)-INDIRECT(D3&1)

Hope it helps

[guil]

Thank you, this is working properly.

I would like to apply this to another sheet (sheet2). For example if I want to determine the difference of C1 and B1, like in the example above, but for sheet2 , but still put C in D2 (sheet1) and B in D3 (sheet 1).
What would the formula look like ?

Thank you very much !!

[Canapone]

Hi,

=INDIRECT("Sheet2!"&D2&1)-INDIRECT("Sheet2!"&D3&1)

Hope it helps

[guil]

Thank you very much Canapone !!
If anybody is aware of an alternate solution, please let me know !
The function works fine, but as the file will be used by serveral users, I am worried that the formula is not very intuitive (when you read it) for people that don't know that function.
Thank you

[guil]

I just tried to do the same formula but linking it to a different file:
=INDIRECT("'[test.xlsx]Sheet1'!"&$D$2&1)-INDIRECT("'[test.xlsx]Sheet1'!"&$D$2&1)
It works when the other file is opened. However, there is a formula error when I close the other file.
Would somebody know how to solve it ?

Thank you !

[NBVC]

INDIRECT() doesn't work with closed files.

Try instead this alternative:

=INDEX([test.xlsx]Sheet1!$1:$1048576,2,CODE($D$2)-64)-INDEX([test.xlsx]Sheet1!$1:$1048576,2,CODE($D$3)-64)


where D2 and D3 contain the column letters to reference... the 2's in the above code reference the row number in the other workbook that the values to subtract from each other are located in.

[guil]

Thank you NBVC.

Since there will be multiple users of the file, this formula is to complicated for a simple substraction.. If that is the only option, I would have to keep the option of the Find-Replace function.

Anyone has a simpler formula ?

Thank you very much,

[Ken Puls]

Guil, I think that you're going to find that trying to create a formula to create a dynamic reference to cells in another workbook is very difficult. Particularly if that workbook is closed. I kind of doubt that anyone is going to come back with a formula that is much simpler than what you've been given.

[NBVC]

The other alternative is to perform the

=INDIRECT("Sheet2!"&D2&1)-INDIRECT("Sheet2!"&D3&1) formula that Canapone gave you in the test.xlsx workbox and then use a simple formula in the active workbook =[test.xlsx]Sheet1!$X$1 where X1 is cell containing the INDIRECT subtration formula in the Test.xlsx workbook..