Referencing a different Workbook
Sorry for the late reply, you have two problems.
1. Enclose the link in single quotes '
2. The brackets go around the workbook name
You want a result of something like this
='C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'!A1
Notice the ' goes in front and before the !
So we get to it with this.
A1: C:\Documents and Settings\kf\Desktop\[2.xls]
A2: =INDIRECT("'"&A1&"Sheet1'!A1")
"Ken" wrote:
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
|