Reference Multiple Excel Workbooks at the same time

Question

I am trying to compile data from multiple workbooks into a single one. So far I had the idea of referencing cells from other workbooks, something like using

='C:\Users\me\Documents\[sample.xlsx]Sheet1'!A1

However typing in each individual workbook this way would be very tedious. Is there away to do something like

='C:\Users\me\Documents\[B2]Sheet1'!A1

Where B2 would have the value of "sample.xlsx".

Answer 1

You could try the following. "REF" is the cell reference containing the target filename. This file needs to be open for this to work, or it will give you a #REF error.

INDIRECT( "'C:\Users\me\Documents\[" & REF & "]Sheet1'!A1" )