2

I have one cell i want to put a file path in, say [C:\users\me\desktop\abc.xlsm]
In another cell reference that text and add on SHEET'$A1 to the end so it references the first cell and then takes the filepath and pulls what is on column A row 1.
I want to do it this way so I can just add in file paths and have the rest of the information auto pull from the file path.
I can't seem to do this.

1

1 Answer 1

4

It is possible to dynamically access a cell in an external workbook, using the INDIRECT function:

=INDIRECT("'C:\PathToFile\[FileName.xlsx]SheetName'!$A$1")

However, there's one significant drawback using this method: the external file must be open, otherwise it will return a #REF! error.

Here is an example with the file open:

enter image description here

And with the file closed:

enter image description here


More information on this answer: INDIRECT gives a #REF! error for reference to external workbook where making a direct reference with an equal sign works. Why?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .