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]

Change

=INDEX(data,10,1)

to

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

 

[blafarm]

Ok, that's what I thought.

So that implies I no longer need the name range of "data".

Is that corrrect?

 

[shg]

Right .

 

[blafarm]

Thank you very much.

Unfortunately, I'm getting the same error message.

"A function is called inside INDEX, which isn't supported.

You have been very helpful, and I really appreciate your time, but maybe I need to contact SpreadSheetConverter with this problem (unless you have an immediate solution).

However, failing that, I would like to thank you in a material way.

Do you have a tip jar -- or something equivalent?

 

[shg]

No, but that's very kind, and you could send a donation in any amount to cdlsusa.org

 

[blafarm]

Will you get a notification if I use this address: consult@mrexcel.com?

 

[blafarm]

Scratch that, they only send acknowledgement cards via snail mail.

 

[blafarm]

Hi shg,

I was sitting here thinking this problem was insurmountable (due to the limitations of SpreadSheetConverter).

I decided to get back to basics with =IF:
=IF($C$5="D1",'D1'!A$5,"")
And, of course it works perfectly.

So then I tested nested =IF:
=IF($C$5="D1",'D1'!B$5,IF($C$5="D2",'D2'!B$5,IF($C$5="D3",'D3'!B$5,IF($C$5="D4",'D4'!B$5,""))))
And it works perfectly too, with the exception that it causes Excel to popup an "Update Values" file name box every time I hit enter.

I understand it is not advisable to nest too many =IF statements, but is there anything fundamentally wrong with this approach?

Or, stated another way, if I have, say, 50 nested =IF statements, does it require more processing cycles than using the =INDIRECT or =INDEX formulas discussed earlier in this thread.

I ask this not because of my computer -- but because of how these sheets perform once they've been converted to webpages by SpreadSheetConverer.

Thanks


Legend
C5 = The cell in my Display sheet that references the name of the desired data sheet
D1 = The name of Datasheet 1
D2 = The name of Datasheet 2
'D[number]'!A$5 = The first cell in a Datasheet that I want displayed in my Display sheet

 

[shg]

I don't have the big picture of what you're trying to do, but ...

o Nested IFs calculate until they get to the expression to return; so if you have 50, then all other things being equal (all tests equally likely, mutually exclusive, and mutually exhaustive), 25 will be evaluated.

o I could probably count on the fingers of both hands the number of times I've used INDIRECT in 30 years of using Excel; it's volatile, and I avoid it if at all possible.

 

[blafarm]

Thank you for those thoughts.