Excel formula to reference cells in another (close

问题:

Here's my desired outcome: I want an Excel workbook (say Master.xls) that I can drop into a directory of other Excel workbooks and Master.xls will extract a given range of cells from all of the hundreds of other workbooks in that directory. I have multiple directories with hundreds of Excel files in each, so I need a Master.xls file that will easily move between directories with different file paths and update based on the files around it in the directory. In my Master.xls file, I can build the file names for all of these other workbooks using text functions like CONCATENATE.

The problem comes when I try to use Excel to reference cells in workbooks that are not currently open. The problems:

1. INDEX can access closed workbooks using hard-coded paths, but can't (as far as I can tell) accept cell ranges as text. To enter cell ranges as text to other functions, one has to use the...
2. INDIRECT function, which doesn't work for closed workbooks.

Basically, INDEX can solve my problem but I can't figure out how to get it to work without hard-coding the paths to the closed workbook into the function call. That's a deal breaker, since I have thousands of workbooks to reference and doing a find-replace to change the file path for each workbook is time-prohibitive and not maintainable.

Other constraints: no Excel add-ins since this sheet has to be shared with others and no VBA because this has to be used by people with fear of macros. I recognize that Excel is not the right tool for this job. Believe me, if I could use another tool, I would.

Update: sample Excel sheet showing the problem:

回答1:

Going straight to the source at MS Office support, INDIRECT does not work with external workbooks.