I have been trying to get the Indirect to work but I keep getting a REF
error. Here is what I tried.

Book "2" has a value of 5 in A1.

In Book 1 I have this.
A1= C:\Documents and Settings\kf\Desktop\2.xls
A2= =INDIRECT("["&A1&"]Sheet1!A1")

AND I tried

In Book 1 I have this.
A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1!
A2= =INDIRECT(A1&"A1")


"Sloth" wrote:

You would have to use the INDIRECT function.

For Instance...
A1: Book1
A2: =INDIRECT("["&A1&"]Sheet1!A1")

A2 references cell A1 in Sheet1 of Book1.

You could shorten it some by using...
A1: [Book1]Sheet1!
A2: =INDIRECT(A1&"A1")

"Ken" wrote:

I have a workbook that references different sheets in a different workbook.
But when I start a new project I must update all the references with the new
filenames using "Replace".

Is it possible to enter the new filename into a cell and just have all my
references point to that cell instead of directly to the other workbook?

Thanks for any help!

Ken