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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200709/1

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

On Sep 26, 1:12 am, "dbvand via OfficeKB.com" <u37702@uwe wrote:
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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200709/1