Linking Files in Excel

[mottawa]

I have to excel files, file A and file B. I am importing several data points
from file A to file B. For example cells in File B have the following values:

'C:\Excel Files\Monthly Data\File A.xls'!Transfer_Final_Amount_Total
'C:\Excel Files\Monthly Data\File A.xls'!Transfer_Total_Discrepency
'C:\Excel Files\Monthly Data\File A.xls'!Transfer_Final_Adjustment

My issue is that if I change the location of File A or even the name of File
A the I must individually remake all of these links to File B. Is there
anyway to do put the location of File A as a reference in File B and thus
just change one thing and all the other references automatically update?

Thank you for any help
Mark

Other

 

[Luke M]

You should be able to do it with the INDIRECT function.

=INDIRECT("'"A2&"'!Transfer_Final_Amount_Total")

(Note the single quotation mark surrounded by regular quotes)

Where in A2 you have entered
C:\Excel Files\Monthly Data\File A.xls

 

[Socko]

One of a way to do is go to Edit in worksheet menu bar, click on Linkscommandbutton "Change Source"...Now select the new path and file and
set it as source. I believe this will work, as it worked on my
system.

I hope this helps...

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com

 

[mottawa]

I keep getting "REF" when I do this. I have tried every single iteration of
this. Is there something I am missing?