pull file path from cell?

[laborspy]

I'm trying to do something that seems pointlessly simple, but I just can't seem to make it work.

I have a sheet that has a file path in A1 and the file itself in A2. I am able to pull the two of them together to use in a formula but it just seems to display the path instead of actually using it. ="'"&A1&"["&A2&"]'"

The other excel file that it is referencing is not open but on a remote network drive. I'd prefer to not have to open both sheets. There a simple method that I am just overlooking?

[Ron Coderre]

1) The source workbook needs to be open for Excel formulas to be able to update the links automatically.
2) You need to convert the text you've built into a valid Excel reference.
Try this:

Please Login or Register  to view this content.

3) If you MUST pull data from closed workbooks, there are VBA alternatives on various websites.

4) If you have direct access to the linked files, you can use: Data.Edit_Links...Update_Values to update the links.
5) Excel does not recognize use of the INDIRECT function to be a link...So the source file would need to be opened for that kind of formula to work.

Does that help?