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

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mottawa" wrote:

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

One of a way to do is go to Edit in worksheet menu bar, click on Links
then select a source whose path has changed now and click on
commandbutton "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

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

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mottawa" wrote:

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