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