Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: link to other file - text reference imbedded

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


Re: link to other file - text reference imbedded

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



Re: link to other file - text reference imbedded

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.