Integrating Excel Worksheet Names dynamically into formula

[TomYC]

For some time I have been looking for a way to do the above, in Index/Match or VLOOKUP type formula referencing in Excel. Basically I want my cell formula to reference a file such as FileSource.xls and one of many worksheets that are datename specific, such as 010513, for January 5th of this year. In this example, I would reference something like: "=INDEX('\\LOCATIONFOLDER\[FILESOURCE.XLS]010513'!$A$2:$A$99),MATCH(etc.)" or something similar. I would think that I could substitute some cell reference for the "010513" portion of the formula. If I could do this, it would be very easy to create a string of cells with formulas identical except for the cell reference that provides the middle portion of the identification of the INDEX array. Can this be done? The background reason, of course, is that I receive data from others that is dumped into individual worksheets/tabs in a single Excel file.

 

[xlbo]

You can use the INDIRECT forumula to create addresses from literal strings

e.g. if your "date" resides in cell A1 then:

=INDEX(INDIRECT("'\\LOCATIONFOLDER\[FILESOURCE.XLS]" & A1 & "'!$A$2:$A$99")),MATCH(etc.)

will do what you need. The downside to INDIRECT is that it will not work on non open workbooks so whether or not you can use it is dependant on your setup

The only other way I can think to do this would be a macro that physically does a find and replace in your formula from a placeholder to a physical reference

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question