Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|