Archive of Mr Excel Message Board
Back to Forms in Excel VBA archive index
Back to archive home
link to other file - text reference imbeddedPosted 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.
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 imbeddedPosted by Dave Hawley on April 17, 2001 5:42 AM
You would use the INDIRECT function for this. Suppose E4 had the text:
In any cell you could use:
OzGrid Business Applications
Re: link to other file - text reference imbeddedPosted 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
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 imbeddedPosted 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
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.