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.

 

[blafarm]

I see. Thank you. Let me try that right now. I will report back shortly. Really appreciate your help.

 

[blafarm]

No.
Go to Name Manager and create a name called myRange. Then use that formula to define it.
No; =INDEX(myRange,5,1)

Got it. Thank you. Received.

I'm sorry. My novice status is surely having me doing something stupid.

This is what I did:

I went to the Name Manager and selected the "New" option
I entered the name: data
At the bottom of the Name Manager window, in the "Refers to" field, I entered the formula copied above (minus the "...")
However, all I get is a #VALUE error
And when I go back into the Name Manager, and select "Edit" to see if I made a mistake, all of the values have changed, and it looks nothing like what I previously entered.

 

[shg]

Sorry, all the ranges should be absolute:

=if($a$1="Sheet1", Sheet1!$A$1:$D$50, if($a$1="Sheet2", Sheet2!$A$1:$D$50, ...))

 

[blafarm]

That example worked! Thank you very much.

However, if one needs to edit a Name Range, why is the "Refers to" field such a mess when selecting "Edit"?

I'm probably doing something wrong -- but this does not appear to be human readable.

Is there no viable way to edit a Name Range after its been created?

 

[shg]

Of course you can edit it.

Press F2 inside Refers To to change Excel from Enter or Point mode to Edit mode -- the state indicator at the bottom left of the Excel screen that usually says Ready when you're not editing a cell.

 

[blafarm]

I see. Sorry.

May I ask another question?

If A1 is blank, or referencing a data sheet that does not exist, it causes my cells with =INDEX(data,10,1) to display a #VALUE ! error.

What is the best way of forcing my cells to be blank in those cases -- instead of #VALUE !?

 

[shg]

=iferror(INDEX(data,10,1), "")

 

[blafarm]

Thank you very much.

I just converted the test spreadsheet and received this unfortunate error:

"A function is called inside INDEX, which isn't supported by this version of the program. A workaround is to move the function to a separate cell."

Is there any way to display the value of Name Range in a cell, like A5?

And, if so, would I simply change the formula to:

=INDEX(A5,10,1)

Thank you for your patience.

 

[shg]

No, that would require INDIRECT. You could use the definition of the name:

=index(if($a$1="Sheet1", Sheet1!$A$1:$D$50, if($a$1="Sheet2", Sheet2!$A$1:$D$50)) , row, col)

 

[blafarm]

I'm sorry, where would I place this?