Using =INDEX with Dynamic Sheets

[blafarm]

I need to dynamically fill select columns in a display sheet (DISPLAY) with select columns from a number of data sheets (D1, D2, D3, ...)

In the DISPLAY sheet, I have a list of friendly names in a column, with their associated data sheet names listed in a column directly to the right.

I am using a Data Validator in the DISPLAY sheet for the friendly names. And I am using INDEX & MATCH in order to populate cell A1 with the names of the actual desired data sheet ((D1, D2, D3, ...).

In the past, I have successfully used INDIRECT for this function, but I am precluded from using it for this application. When I have used it in the past to construct dynamic sheet references, I have used this technique to construct and reference the dynamic sheet:

=INDIRECT("'"&$A$1&"'!"&____) Cell A1 is the data sheet name.

However, this dynamic reference is not working with =INDEX, which requires a sheet name and reference range.

I have proven that I can get what I need with an absolute reference to the sheet like this:

=INDEX('D1'!A1:D50,0,1)

However, all attempts to construct something dynamic using =INDEX have failed, including this:

=INDEX("'"&$A$1&"'!"A1:D50,5,1)

With all of my attempted variations, Excel either won't accept the formula, or will return a #VALUE error.

I suspect my problem is related to the cell range which needs to be incorporated with the dynamic sheet name -- but I don't know what I am doing wrong.


Any assistance would be greatly appreciated.

 

[shg]

You're welcome.

... 25 will be evaluated

That should say that 25 conditions will be evaluated (on average), but only the one expression to be returned.