Excel formula to reference cells in another (closed) workbook with dynamic file path
<p>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.</p>

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

<ol>
<li>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...</li>
<li>INDIRECT function, which doesn't work for closed workbooks.</li>
</ol>

<p>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.</p>

<p>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.</p>

<p>Update: sample Excel sheet showing the problem:</p>

<p><a href="https://i.stack.imgur.com/XGZTu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XGZTu.jpg" alt="Excel output"></a></p>