INDIRECT only works with cell references and ranges, not
formulas. But there may be a work around. Follow me on my
example:
I have a dynamic range of numbers I want to sum. My "jan"
list is D1:Dn, and "feb" is in E1:En.
I set up 4 defined names as such:
jan =Sheet1!$D$1
jan1 =Sheet1!$D:$D
feb =Sheet1!$E$1
feb1 =Sheet1!$E:$E
The user selects a month in the drop-down in cell B1, and
I use the following formula to get the sum:
=SUM(OFFSET(INDIRECT(B1),,,INDIRECT(B1&"1")))
HTH
Jason
Atlanta, GA
-----Original Message-----
Hello everyone,
I'm having trouble using a dynamic named range through
Indirect.
I have a dropdown in cell C1 that selects the month. Each
month has a static named range. I call the month in a
formula that uses Indirect. This worked fine until I
tried
using dynamic named ranges for the months.
When using dynamic ranges INDIRECT($C$1) now evaluates to
#REF!. If I change back to static named ranges then the
formula once again works properly.
The formula for the dynamic range is correct:
=OFFSET(2004!$C$1,0,0,COUNTA(2004!$A:$A)-1,1)
Any ideas?
.
|