How to insert a cell reference into a link to another workbook when pulling data in?

[Matts915]

I have a main workbook that I am using, and need to pull in data from other workbooks into various cells.   I have a ton of other workbooks that I am pulling from, and going into each one is onerous, particularly because the cell reference never changes, just the name of the workbook.   So I have a directory/reference structure like this when pulling data into my main workbook: 

=+'C:\data\jeff\[jeff.xlsx]MainPage'!C$15

=+'C:\data\chris\[chris.xlsx]MainPage'!C$15

=+'C:\data\larry\[Larry.xlsx]MainPage'!C$15

The only thing that changes as I pull in the data is the name of the sub-directory and the sheet.  I have those names already in my master workbook.  What I would want to do is something like this: 

=+'C:\data\$A1\[$A1.xlsx]MainPage'!C$15

=+'C:\data\$A2\[$A2.xlsx]MainPage'!C$15

=+'C:\data\$A3\[$A3.xlsx]MainPage'!C$15

Where the cell references are the names...that way I can just click and drag.  Unfortunately no matter how hard I try to get a cell reference into these paths it gives me an error.  I've used Excel for a long time and tried every trick I know. Is this just not possible? 

[#2]

@Matts915 

The problem is that the INDIRECT function that can be used to convert text strings to a reference, only works with references to another workbook if that workbook is open in Excel. If the other workbook is closed, INDIRECT returns #REF!

Since you have many other workbooks, having all of them open in Excel doesn't look like a viable option.

If possible, combine the workbooks into a single workbook.

Otherwise, you might use a macro to create the formulas for you.

[#3]

Yeah, INDIRECT was as close as I got to it working....unfortunately I cannot combine them. I'll have to try a macro as a next attempt; I wanted to ask here first to make sure I was not missing something obvious.