|
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
Index and named ranges selecting difficulty
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? |
| All times are GMT +1. The time now is 12:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com