link to other file - text reference imbedded

Posted by Tim Clyne on April 17, 2001 5:27 AM

I would like to link to another file with a formula that references the filename in another cell.

for example,
A1 has text: filename.xls
A2 is a formula: ='[filename.xls]sheet1'!B1
i want the filename in A2 to be what is in A1

having difficulty writing the formula

Posted by Dave Hawley on April 17, 2001 5:42 AM

Hi Tim
You would use the INDIRECT function for this. Suppose E4 had the text:
[Book2]Sheet1!

In any cell you could use:

=INDIRECT(E3&"A1")

Dave

OzGrid Business Applications

Posted by Tim Clyne on April 17, 2001 6:50 AM

Thanks for the quick response. The INDIRECT function is the one I have been trying to use, but I still get the #REF error.

here is the exact problem.
in cell B2 I have

'D:\files\CollegeHire\acct dev\surveys\respondents\[aman, jarrod.xls]survey'!

I want cell H13 in the aman, jarrod.xls file to come into my current file. So, in cell C2 of my current file I have

=INDIRECT(B2&"$H$13")

I tried cell B2 with and without the single quotes at the beginning and near the end

I still get the #REF error

any help is greatly appreciated

Posted by mseyf on April 17, 2001 10:37 AM

I don't think INDIRECT can reference a closed workbook. you could try attaching a 'search and replace' macro to a change event for cell A1

HTH

Mark