Indirect and dynamic ranges

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?

Well, apparently Indirect will not accept a dynamic named
range as an argument.

So, how can I work around this?

C1 is the dropdown that lists the months.

Here's the portion of the formula that references C1:

....SMALL(IF(INDIRECT($C$1)<>""......

How can I select Jan from the dropdown and end up with
this without hard coding:

....SMALL(IF(Jan<>""......

Jan is the dynamic range that refers to Sheet1!C2:Cn

Any ideas?

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

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

Jason, thanks for the reply.

Well, to be honest my goal was to come up with a work-
around for Indirect so that I could use a formula based
dynamic named range.

I have come up with that work-around. It involves using
Choose and Vlookup. It's a real hack, for sure, but it
works.

>-----Original Message-----
>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?
>>.
>>
>.
>