Index and named ranges selecting difficulty

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bruce Tharp - 17 Jun 2007 18:46 GMT

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?

Reply to this Message

Bob Phillips - 17 Jun 2007 19:01 GMT

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

etc.

Signature

HTH

Bob

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

>I have a workbook that uses index that have worked well. I now have to
>index
[quoted text clipped - 13 lines]
>
> Any help?

Reply to this Message

T. Valko - 17 Jun 2007 19:22 GMT

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

> INDIRECT("'"&c1&"'!$A$2:$F$6")
>
[quoted text clipped - 17 lines]
>>
>> Any help?

Reply to this Message

Bruce Tharp - 17 Jun 2007 22:09 GMT

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.

> You should be able to replace this:
>
[quoted text clipped - 27 lines]
> >>
> >> Any help?

Reply to this Message

ShaneDevenshire - 17 Jun 2007 20:08 GMT

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...

Signature

Cheers,
Shane Devenshire

> INDIRECT("'"&c1&"'!$A$2:$F$6")
>
[quoted text clipped - 17 lines]
> >
> > Any help?

Reply to this Message

Bruce Tharp - 17 Jun 2007 22:07 GMT

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

> Hi,
>
[quoted text clipped - 33 lines]
> > >
> > > Any help?

Reply to this Message

Bob Phillips - 17 Jun 2007 23:20 GMT

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

Signature

HTH

Bob

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

> Hi,
>
[quoted text clipped - 38 lines]
>> >
>> > Any help?

Reply to this Message

ShaneDevenshire - 19 Jun 2007 02:49 GMT

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.

Signature

Cheers,
Shane Devenshire

> You can but taking the single quotes away from the sheet names is not a good
> idea.
[quoted text clipped - 41 lines]
> >> >
> >> > Any help?

Reply to this Message