Hello,
I have several workbooks that have identically formatted data which I need to collate into a single location.

I would like to use a variable in each reference in the collating worksheet. What I've encountered is:

Using =INDIRECT("'["&A2&".xls]Sheet1'!$C$10") where A2 contains workbook name:
- INDIRECT() works, but only when all the worksheets are simultaneously open.
- Requires closing and reopening of collating worksheet to get updates.

Pivot Tables:
- I have not been able to get any similarly defined string to work in the Range field in the Pivot Table Wizard.
- I believe the same restrictions for INDIRECT() would still exist (ie, the other worksheets would need to be open simultaneously)

QUESTION: Is there another way to (a) get the data using varables (cell contents) and (b) having be able to be updated at the push of the Refresh All Data button?

THANK YOU in advance for your help!
Vince