Sum using INDIRECT & Dynamic Named Ranges

G

Guest

I am trying to use the INDIRECT function to sum a named range. It works fine
for static names, but not dynamic names.

Simplified example:
1. I have named ranges in B2:B10 called "Totals" and C2:C10 called
"Averages" (both ranges contain numeric values)
2. Cell D1 has data-validation ... list of "Totals", "Averages"
3. Cell E1 has the formula =SUM(INDIRECT(D1))

This will sum either the Totals column or Averages column, depending on what
I select in cell D1 from the drop down menu. This works fine as is, but if
the named ranges are dynamic, it doesn't work.

For instance, if the name "Totals" is
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A2:$A500),1). This name allows me to
add up to 500 items in my list without changing my formulas. But now the
formula in E1 returns an error. To check, =SUM(Totals) returns the correct
answer, even with dynamic names. Why does INDIRECT only work with static
names, or am I doing something wrong that I don't see?

One work-around would be to use an "IF" statement in E1, but this gets
cumbersome if I am using dozens of dynamic names instead of just this
simplified illustration with only two (i.e. if cell D1 had 25 drop down lines
instead of just two).

Any ideas?
 
G

Guest

Found the answer to my own question...sort of. INDIRECT only works on
references, not formulas, and the dynamic names are formulas.

Any ideas how to work around this? I'm surprised no one has take a crack at
this yet. If the example isn't clear, let me know and I'll try to clarify.

Thanks!
 
H

hgrove

steveEx wrote...
...
. . . Why does INDIRECT only work with static names, or am I doin something
wrong that I don't see?

One work-around would be to use an "IF" statement in E1, but thi gets
cumbersome if I am using dozens of dynamic names instead of just this
simplified illustration with only two (i.e. if cell D1 had 25 dro down lines instead
of just two).

So Microsoft isn't letting you reach your potential today?

This is just the way INDIRECT works. As for your situation, have Tota
and Average refer to the topmost cell in their respective columns, the
use OFFSET in your sum formulas.

=SUM(OFFSET(INDIRECT(D1),0,0,COUNTA(Sheet1!$A2:$A500),1)
 
G

Guest

Brilliant, Thanks.

hgrove said:
steveEx wrote...
...

So Microsoft isn't letting you reach your potential today?

This is just the way INDIRECT works. As for your situation, have Total
and Average refer to the topmost cell in their respective columns, then
use OFFSET in your sum formulas.

=SUM(OFFSET(INDIRECT(D1),0,0,COUNTA(Sheet1!$A2:$A500),1))
 
G

Guest

Thanks. this also works. didn't think about using CHOOSE. the formula
still works with 25 variables but gets a little long
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top