If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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? . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Indirect Function ??? | Tony | Worksheet Functions | 2 | August 31st, 2004 03:51 PM |
Indirect function on rows | Alex | Worksheet Functions | 3 | August 31st, 2004 07:38 AM |
INDIRECT function | Dan Wilson | Worksheet Functions | 1 | February 26th, 2004 07:29 AM |
INDIRECT function limitations | Brian | Worksheet Functions | 4 | January 3rd, 2004 12:53 AM |