Sep 26 2022 08:43 AM
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?
Sep 26 2022 08:49 AM
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.
Sep 26 2022 10:00 AM