Index function to pull value from a closed workbook, but use cell value for sheet name

0
votes

I have 2 workbooks and within each book, each sheet are the months of the year. The source book and target book have identical sheet names, eg. Aug 2019, Sep 2019..... I have created the following formula in the target book, cell B9, to pull a value from the source workbook, cell AJ46:

=INDEX('C:\FTPDownloads\Villa Stuff[Occupancy Chart Rev 1d.xlsx]Aug 2019'!$AJ$46,1)

It works fine, even with the source book closed.
In the target book I have loaded cell Q3 with the following formula :

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This loads cell Q3 in the target book with the current active sheet. So cell Q3's value is Aug 2019. There are 20 copies of the INDEX formula in the target book, and the only thing that changes is the source cell at the end, which in the formula above is AJ46. To prevent constant changing of the INDEX formula when I extend the sheets to future months, I would like to use the value in cell Q3 in place of entering the actual sheet name. I have tried :

=INDEX('C:\FTPDownloads\Villa Stuff[Occupancy Chart Rev 1d.xlsx]"&Q3&"'!$AJ$46,1)

But it produces an error. I am using Excel 2007.

1
excelfunctionindexing

1 Answers

0
votes

Firstly, if you only want to return a specific cell, say AJ46, why won't you just reference the cell address directly rather than using an INDEX function? I am sure the following will also return the desired value regardless if the other workbook is open or not.

='C:\FTPDownloads\Villa Stuff[Occupancy Chart Rev 1d.xlsx]Aug 2019'!$AJ$46

Secondly, there have been ample discussions on this topic online and even in this community as in this post. The simple conclusion is:

there is no way to make a dynamic workbook/worksheet/range/cell name using excel formula and return the desired value from a closed workbook.

Thirdly, it can be done using VBA. If it is an option for you, you may want to add #vba to your tags so other contributors may be able to help you as #vba it not my expertise. Someone in the post I mentioned before provided a vba code that works for direct cell reference but not incorporating INDEX. If that's an option for you you may want to give it a go.

Fourthly, for your information it can also be done using PowerQuery but it is only available in Excel 2010 and later versions. Besides, PowerQuery worked best with large raw data-set rather than structured report anyway.