Named Range Error

[How_Do_I]

Hello,can anyone tell me where I’m going wrong with this please…<o></o>
E33 isreturning the expected value when I use a named range called “Season13” whichis defined as =Sheet1!$CD$5:$CK$556<o></o>
<o></o>
However when I redefine "Season13" to =Sheet1!$CD$5:INDEX(Sheet1!$CK$5:$CK$556,Sheet1!$CD$2)where $CD$2 =552 I’m getting a #REF error… when I check this name in Insert/Namesthe “dancing ants are in the correct place which is really confusing me.<o></o>
<o></o>
What am I doing wrong please?<o></o>
<o></o>

<o> </o>​	E<o></o>​
33<o></o>​	1 - 1<o></o>​

<tbody>
</tbody>

<o></o>

Spreadsheet Formulas<o></o>

Cell<o></o> Formula<o></o> E33<o></o> =IF(ISNA($B33),"",INDEX(INDIRECT("Season"&A33),$B33,4)&" - "&INDEX(INDIRECT("Season"&A33),$B33,5))<o></o> <tbody> </tbody> <o></o>

<tbody>
</tbody>

<o></o>

 

[Danerida]

Try:

[FONT=Calibri][COLOR=#000000]=Sheet1!$CD$5[/COLOR][/FONT]&":"&[FONT=Calibri][COLOR=#000000]INDEX(Sheet1!$CK$5:$CK$556,Sheet1!$CD$2)[/COLOR][/FONT]

 

[Teeroy]

The formula does not return a range directly. Since you are including a variable you'll probably need the indirect function.

Try defining the Name as =INDIRECT("Sheet1!$CD$5:$CK$"&Sheet1!$CD$2+4)

 

[How_Do_I]

No luck with those suggestions, has anyone else got any ideas please? Thanks for the help so far though...

 

[ukmikeb]

Hi

If you just select the area CD5 to CK556 when defining "Season13", as a named range, then you have the flexibility to make adjustments to extract the data.

The reason you are getting the #Ref error is because the result of "INDEX(Sheet1!$CK$5:$CK$556,Sheet1!$CD$2)" is a value and not an address.

hth

 

[Danerida]

If your data in column CD is contiguous (no blanks), for your named range you could use:
=OFFSET(Sheet1!$CD$5,,,COUNTA($CD:$CD))

 

[How_Do_I]

Hi Danerida...Your solution didn't work for me... <o></o>
<o> </o>
Defining"Season13" as=Sheet1!$CD$5:OFFSET(Sheet1!$CD$5,,7,COUNTA(Sheet1!$CK:$CK)) or =Sheet1!$CD$5:INDEX(Sheet1!$CK$5:$CK$556,Sheet1!$CD$2)where $CD$2 =552 highlights the correct range when checking in the name manager but both return a #REF error to my formula... <o></o>
<o> </o>
ukmikeb must beright, these formulas are returning a value not a cell address. Defining Season13 as =Sheet1!$CD$5:$CK$556 isthe only formula that is working.<o></o>

 

[RoryA]

Indirect doesn't work with dynamic ranges, only literal addresses.