Forum Discussion
Solved
Use formula to determine which workbook to reference
I have one workbook which must compile data from many smaller workbooks.
In column A of the main workbook, I have the names of the smaller workbooks which need to be referenced. I need to dynamically reference each workbook individually.
I have attempted to do this using the INDIRECT function, but have had no luck.
For example, if I wanted to reference cell B6 in the small workbook in the A2 cell of the main workbook, I'd write:
=INDIRECT("["&"A2"&".xls]B6
This isn't working for me. Any suggestions?
Liam502 Try tis:
=INDIRECT("["&A2&".xls]Sheet1!B6
Replace Sheet1 with the actual name of the sheet.
And note that the other workbook must be open. Otherwise INDIRECT will not work.
Liam502 Try tis:
=INDIRECT("["&A2&".xls]Sheet1!B6
Replace Sheet1 with the actual name of the sheet.
And note that the other workbook must be open. Otherwise INDIRECT will not work.
