Excel Question

[dbvand via OfficeKB.com]

My problem is a little difficult to explain so please bear with me.

When you link a cell in one spreadsheet to a cell in another spreadsheet, the
first part of the formula is the filename of the other spreadsheet. For
example
='[Book1.xls]Sheet1'!$A$4 where Book1.xls is the filename of the other
spreadsheet.

Is there a way that I can make that formula refer to a cell in the current
spreadsheet to get the filename of the other spreadsheet it is supposed to
refer to? That way by changing the filename in only 1 place in the
spreadsheet it would change all of the formulas that refer to the other
spreadsheet.

Thanks in advance for any help.
Bryan

 

[Pete_UK]

The way you would normally do that is with INDIRECT, whereby you can
build up a cell reference as if it were a string. However, INDIRECT
will only work with open workbooks - can you guarantee that Book1.xls
will be open?

Hope this helps.

Pete