How to use a cell as a filename in a reference in

Question

If I have a cell, "a2" which has part of a filename/filepath, "sheet2.xlsx" in it which I want to use as a reference another workbook, how can I accomplish this?

I have tried using the INDIRECT function but always get a reference error.

I am trying to find a way to summarize a list of spreadsheets with column a containing the spreadsheet name and another column referencing a cell inside that respective sheet.

Answer 1

If the referenced workbook is open, then here is an example of getting a value from it:

=INDIRECT( "'" & A1 & "[" & B1 & "]" & C1 & "'!" & D1)

where the components are like:

Note we have specified:

1. the path
2. the filename
3. the worksheet name
4. the cell address

If the file is not open, you can still retrieve data with a ExecuteExcel4Macro (assuming you have a version of Excel that supports this function) For an example of this type of code, see:

Get Data from External Files