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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

You should be able to replace this:

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

With:

HCS!E3+1

Biff

"Bob Phillips" wrote in message
...
INDIRECT("'"&c1&"'!$A$2:$F$6")

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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.

"T. Valko" wrote:

You should be able to replace this:

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

With:

HCS!E3+1

Biff

"Bob Phillips" wrote in message
...
INDIRECT("'"&c1&"'!$A$2:$F$6")

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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

--
Cheers,
Shane Devenshire


"Bob Phillips" wrote:

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

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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

"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire


"Bob Phillips" wrote:

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

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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)

"ShaneDevenshire" wrote in
message ...
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...

--
Cheers,
Shane Devenshire


"Bob Phillips" wrote:

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

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?

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.

--
Cheers,
Shane Devenshire


"Bob Phillips" wrote:

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)

"ShaneDevenshire" wrote in
message ...
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...

--
Cheers,
Shane Devenshire


"Bob Phillips" wrote:

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

etc.

--
HTH

Bob

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

"Bruce Tharp" <Bruce wrote in message
...
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,I F(HCS!D3<=Matrix!$A$4,3,IF(HCS!D3<=Matrix!$A$5,4,I F(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?