Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indirect Function Doesn't like non-contiguous ranges
I was trying to use the indirect command to sum a named
range of cells. If I enter the following in a cell then the formula works if the text in the cell refers to a contiguous range. If I change the range to be non- contiguous, it gives a #ref error in the cell. Cell contents =+SUM(INDIRECT(I66)) I66 has the string "rng1" which is a named range on the current worksheet. Does anyone have any idea of any work around? |
#2
|
|||
|
|||
Please study CHOOSE function. Regards.
|
#3
|
|||
|
|||
I don't think you can use indirect for this
=sum(rng1) will work -- Don Guillett SalesAid Software wrote in message ... I was trying to use the indirect command to sum a named range of cells. If I enter the following in a cell then the formula works if the text in the cell refers to a contiguous range. If I change the range to be non- contiguous, it gives a #ref error in the cell. Cell contents =+SUM(INDIRECT(I66)) I66 has the string "rng1" which is a named range on the current worksheet. Does anyone have any idea of any work around? |
#4
|
|||
|
|||
-----Original Message----- Please study CHOOSE function. Regards. THanks, I looked at Choose function and it allows me to choose from a list that has set range names, but it doesn't let me use another cell for the range name as can be done with indirect (except for non-contiguous ranges). Any one have another ideal. |
#5
|
|||
|
|||
I'm not sure if this will help, but assuming that your range of
non-contiguous cells are A1, B4:B6, and C7:C12, try... =SUMPRODUCT(SUMIF(INDIRECT(E1:E3),"<")) ....where E1:E3 contains the following references: E1: A1 E2: B4:B6 E3: C7:C12 Hope this helps! In article , wrote: I was trying to use the indirect command to sum a named range of cells. If I enter the following in a cell then the formula works if the text in the cell refers to a contiguous range. If I change the range to be non- contiguous, it gives a #ref error in the cell. Cell contents =+SUM(INDIRECT(I66)) I66 has the string "rng1" which is a named range on the current worksheet. Does anyone have any idea of any work around? |
#6
|
|||
|
|||
Thanks for all replies. You all have confirmed that the
indirect function won't operate on non-contiguous ranges. Don, you are correct that entering the range directly will work, but that prevents me from doing string math to create the range names that I want. Sometimes we just want to do more than the program will do. Thanks again for your time. At least I know I hit a dead end. -----Original Message----- I don't think you can use indirect for this =sum(rng1) will work -- Don Guillett SalesAid Software wrote in message ... I was trying to use the indirect command to sum a named range of cells. If I enter the following in a cell then the formula works if the text in the cell refers to a contiguous range. If I change the range to be non- contiguous, it gives a #ref error in the cell. Cell contents =+SUM(INDIRECT(I66)) I66 has the string "rng1" which is a named range on the current worksheet. Does anyone have any idea of any work around? . |
#8
|
|||
|
|||
Any would have a solution to suggest? I am encountering the same issue... INDIRECT with non continous range... I am stuck there.
Is it considered as a bug or a limitation of Excel? I don't understand. Anyway, Excel 2003, 2007 and 2010 have same behavior... Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indirect worksheet function in excel | Excel Worksheet Functions | |||
INDIRECT function error | Excel Discussion (Misc queries) | |||
INDIRECT function question | Excel Worksheet Functions | |||
Indirect( ) function loosing values when spreadsheets are closed | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |