Use one cell to hold File reference

Hi I have a sheet with links to external workbooks, an example of a cell formula would be:

='C:\[Workbook1.xls]Sheet2'!M59

Everytime i change external workbook I have to change all the references in all of the cells. What I want to do is store the filereference in cell A1 like:

A1 would be C:\Workbook1.xls

And the rest of the cells in the sheet gets their tar开发者_如何学运维getfile from this cell... Then I could just change the fileref and update links and the data shown in the sheet would point to the new external workbook. Is this possible?

This should do it:

=INDIRECT("'"&LEFT(A1,3)&"["&RIGHT(A1,LEN(A1)-3)&"]Sheet2'!M59")

you seem to be introducing extra complexity with having different workbook names, can u leave the workbook name constant?

if you can't then you can use INDIRECT function to convert text into actual function calls.

Might also help if you use named Ranges (so that if cell location changes in the other workbook, it will still work)