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.
-
You may want to read What should I do when someone answers my question?– Tom BrunbergJun 7, 2018 at 6:37
1 Answer
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:
And with the file closed:
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?