Changing formula reference

guil

New member
Joined
Nov 22, 2012
Messages
5
Reaction score
0
Points
0
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

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
Excel 2010
Hi,

a first example could be:

D2 houses C, D3 houses B

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

Hope it helps
 

guil

New member
Joined
Nov 22, 2012
Messages
5
Reaction score
0
Points
0
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

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
Excel 2010
Hi,
Code:
=INDIRECT("Sheet2!"&D2&1)-INDIRECT("Sheet2!"&D3&1)
Hope it helps
 

guil

New member
Joined
Nov 22, 2012
Messages
5
Reaction score
0
Points
0
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

New member
Joined
Nov 22, 2012
Messages
5
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Nov 22, 2012
Messages
5
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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..
 
Top