Possibly Index? - Reference a workbook using a cell in a current worksheet

[blens1]

I am looking to roll-up a lot of information by referencing multiple workbooks and specific cells and I believe I have done this before using INDEX() but it has been a long time.

As an Example Column A has the file's name it should look at, Column B has the tabs name it should look at, Column C has the cell location that should be referenced.

As it currently stands the direct formula is =[chicagotest.xlsx]sheet!D5. I have 40 workbooks it needs to reference. What formula can read column A (file name), column B (sheet name) and column C (cell location) in other files for me.

[Richard Buttrey]

What about the path to the filename? Is that held in a cell somewhere and is it common to all files?

[José Augusto]

I suppose you must use INDIRECT

Try

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Richard Buttrey]

Unfortunately INDIRECT() won't work with closed workbooks.
One option would be to iterate through all the file names, open them create the formula and then close the workbook.

[Richard Buttrey]

...one VBA macro that seems to work is the following.

Assuming the path is common to all files and with the path in say E1

Please Login or Register  to view this content.

Change the loop counter end value as necessary or better still use a variable to set it.

[Richard Buttrey]

...one VBA macro that seems to work is the following.

Assuming the path is common to all files and with the path in say E1
Filename in column B
Sheetname in column C
cell ref in column D

Please Login or Register  to view this content.

Change the loop counter end value as necessary or better still use a variable to set it.