dynamic named ranges

[daveyc18]

right now I have a named range named "months" which is from ='Table 1'!$C$1:$AC$1. but, sometimes new months will be added, so right now the range has been extended to ='Table 1'!$C$1:$AK$1. Is there a way to make this dynamic?

Ditto for the named range "data," whihc is currently ='Table 1'!$C:$AC

 

[Eric W]

Try:

months:

=OFFSET('Table 1'!$C$1,0,0,1,COUNTA('Table 1'!$C$1:$ZZ$1))

data:

=OFFSET('Table 1'!$C$1,0,0,1048576,COUNTA('Table 1'!$C$1:$ZZ$1))

 

[daveyc18]

ok, i was able to do it like this

"=OFFSET('Table 1'!$C$1,0,0,COUNTA('Table 1'!$C:$C)+28,COUNTA('Table 1'!$1:$1))"

and named it "data_all," but doing so gives me a VALUE error with this formula

=XLOOKUP($A3,months,XLOOKUP(B$2,'Table 1'!$B:$B,data_all,0))/1000

not sure why, because when I click into the named range to see the range it's covering, it's capturing everything from C1 to the last row of AK

if i dont use the named range and simply use the columns, it's fine:

=XLOOKUP($A3,months,XLOOKUP(B$2,'Table 1'!$B:$B,'Table 1'!C:AK,0))/1000

 

[daveyc18]

it's oK...i just made the formula like this:

=XLOOKUP($A3,'Table 1'!$C$1:$XFD$1,XLOOKUP(B$2,'Table 1'!$B:$B,'Table 1'!C:XFD,0))/1000

not the most elegant, but that's fine ...I'm guessing expanding the range to the maximum makes the spreadsheet slower even tho xlookup isn't a volatile formulka?

 

[Eric W]

XLOOKUP isn't volatile, but OFFSET is. You could try something like

months:

=Sheet6!$C$1:INDEX(Sheet6!$1:$1,COUNTA(Sheet6!$C$1:$ZZ$1)+3)

data:

=Sheet6!$C$1:INDEX(Sheet6!$A:$ZZ,1048576,COUNTA(Sheet6!$C$1:$ZZ$1)+3)

which uses INDEX instead to see if that makes it faster.