Index and named ranges selecting difficulty

[Guest]

I have a workbook that uses index that have worked well. I now have to index
and chose a named range based on the value of a cell. I'm having a tough
time understanding how to get the index to assign the correct range based on
the cell value.

=INDEX(Matrix!$A$2:$F$6,IF(HCS!D3<=Matrix!$A$3,2,IF(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,IF(HCS!D3>=Matrix!$A$6,5,0)))),IF(HCS!E3=1,2,IF(HCS!E3=2,3,IF(HCS!E3=3,4,IF(HCS!E3=4,5,IF(HCS!E3=5,6,0))))))/10000
The above works great.
Now I need to change the "Matrix!" to change depending on what the value in
the cell $C2 changes to. ie: Matrix or TNP, or any of 12 other options.
There is a range matching the valuse of each possible selection in the cells
in column C.

Any help?

 

[Bob Phillips]

INDIRECT("'"&c1&"'!$A$2:$F$6")

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

You should be able to replace this:

IF(HCS!E3=1,2,IF(HCS!E3=2,3,IF(HCS!E3=3,4,IF(HCS!E3=4,5,IF(HCS!E3=5,6,0)))))

With:

HCS!E3+1

Biff

 

[Guest]

Hi,

INDIRECT("'"&c1&"'!$A$2:$F$6")

I think you can simplify this to

=INDIRECT(C1&"!A2:F6")

I believe that since the cell refernces are quoted that has the same affect
as using absolute references.

In all replace:
Matrix!$A$2:$F$6 with the above
Matrix!$A$3 with =INDIRECT(C1&"!A3")
and so on...

 

[Guest]

Thanks very much. This worked very well. I appreicated the replies

 

[Guest]

Thanks. That would work now, however, shortly the final IF statement has to
change to IF <= Then.... because the result could be greater than 5.

 

[Bob Phillips]

You can but taking the single quotes away from the sheet names is not a good
idea.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Ah! I see your point, your answer is a generalized response and not
specific to the sample data.

If you enter sheet names with a space in the cell for the INDIRECT command
you need the single quotes, or you need to enter the sheet name as follows
into the reference cell:

''My Sheet'

In this case the first '' is two single quotes.

Then INDIRECT will work without the quotes within the formula.

But I agree with you that if the user is going to name their sheets with
spaces in the names, its better to put the quotes in the formula as you did,
not into the cell.