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
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
<[email protected]> 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
>[email protected]
><[email protected]> wrote in message
>news:[email protected]...
>> 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
[email protected]
"Mel" <[email protected]> wrote in message
news:[email protected]...
> 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
> >[email protected]
> ><[email protected]> wrote in message
> >news:[email protected]...
> >> 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?
> >
> >
> >.
> >