Indirect Function Doesn't like non-contiguous ranges

[Guest]

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?

 

[tkt_tang]

Please study CHOOSE function. Regards.

 

[Don Guillett]

I don't think you can use indirect for this
=sum(rng1) will work

 

[Guest]

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

 

[Domenic]

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!

 

[Mel]

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.

 

[Don Guillett]

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