Quote:
Originally Posted by Spencer101
Hi Kirk,
Set up as your example above, the following formula in cell A1 should do the trick.
=INDIRECT("'[filename.xlsx]"&B1&B2&"'!"&C1&C2)
This would assume the two files are in the same directory.
Hope that helps.
S.
|
It appears that the INDIRECT function only works when both files are open. I have tried to reference both filename.xlsx and the full address 'C:\dir\filename.xlsx. Both work as long as the file is open; as soon as you close the referenced file, and recalculate the worksheet with the formula, it returns a #REF! error.
I can't force users to open the other file, so I need a solution that will work when it is closed. Anyone know a way other than INDIRECT, or perhaps I am missing something in the way INDIRECT works?