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]

=index(indirect($a$1 & "!a1:d50"), 5, 1)

 

[blafarm]

Hi shg,

Thanks for your very prompt reply.

Unfortunately, I cannot use =INDIRECT because that function is not supported by SpreadSheetConverter.

The functions that SpreadSheetConverter supports are: https://www.spreadsheetconverter.com/products/supported-excel-functions

Any other way to skin this cat?

 

[shg]

INDIRECT is the function that makes text into a range reference, so no.

EDIT:

but I am precluded from using it for this application.

Missed that.

 

[blafarm]

Thanks again for your quick response.

I'm a bit of a novice, so please excuse this question if it make no sense.

We know that I can easily display the value of any cell in my data sheets, by simply pasting this equation into a cell of my display sheet:

=+'D1'!A1

Is there no other conditional function that allows me to replace the:

'D1'!

...with a text string from another cell?

 

[shg]

A way, perhaps;

Create a named formula ("myRange") Refers to:

=if(a1="Sheet1", Sheet1!A1:D50, if(a1="Sheet2", Sheet2!A1:D50, ...))

 

[shg]

Then the formula would be =index(myRange, whatever)

That would get tiresome if you needed a lot of them -- and assumes your converter supports named formulas.

 

[blafarm]

Ok, so if I am understanding you correctly, I think you are suggesting:

1. Give a new cell in my display sheet (A2 for example) the name of "myRange"

2. Insert the formula you supplied directly above

3. Modify my original formula in my desired cells so that it looks like this: =INDEX("myRange,5,1)

Did I get that right?

 

[blafarm]

Sorry, I should have written that formula as: =INDEX("myRange",5,1)

 

[shg]

No.

Go to Name Manager and create a name called myRange. Then use that formula to define it.

Sorry, I should have written that formula as: =INDEX("myRange",5,1)

No; =INDEX(myRange,5,1)