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?

Please study CHOOSE function. Regards.

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?

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

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?

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?


.

sorry I couldn't have been of more help.
You could write a UDF (custom designed formula) to do this.

--
Don Guillett
SalesAid Software

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


.

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,