Best way to name Dynamic ranges

I've always used OFFSET like so

keycolumn = OFFSET($A$2,,,COUNTA($A$2:$A$5000))
Second column =OFFSET(keycolumn,,1)
etc.

The problem with that is blanks will screw up everything.

I've seen on here (can't find the thread but I think it was Donkeyote

EndRow =MATCH(9E+307, $A:$A, 1)
Then
keycolumn = INDEX($A:$A, 2):INDEX($A:$A, EndRow)
Second Column =INDEX($B:$B, 2):INDEX($B:$B, EndRow)
etc.

Any disadvantages to using this technique?
How about combining the two and developing the key column with method 2 and then using the OFFSET for other columns.

Any other ways to set up this type of dynamic range?

You could replace your Offset() formula with:

=OFFSET(Sheet1!$J$1,0,0,COUNTIF(Sheet1!$J:$J,"*?"),1)

this will cut out the formula blanks at the bottom of the range (if any).

I think you're saying you need to determine the last row containing a numeric value and use that to determine your named range -- where interspersed amongst the values you may have blanks... ie using COUNT type approach will generate misleading ranges, correct ?

One way is to use something along the lines of:

Name: _test
RefersTo: =OFFSET($A$2,0,0,MATCH(9.99999999999999E+307,$A:$A),1)

This would account for blanks interspersed in the range, HOWEVER, would only work were the last value numeric... if you had text etc below the last number the above would ignore that.

So you could in theory create the following:

Name: BIGNUM
RefersTo: 9.99999999999999E+307

Name: BIGTXT
Refers To: =REPT("z",255)

Name: _lastno
RefersTo: =LOOKUP(BIGNUM,CHOOSE({1,2},0,MATCH(BIGNUM,$A:$A)))

Name: _lasttxt
RefersTo: =LOOKUP(BIGNUM,CHOOSE({1,2},0,MATCH(BIGTXT,$A:$A)))

Name: _lastlogic
RefersTo: =LOOKUP(BIGNUM,CHOOSE({1,2},0,MATCH(TRUE,$A:$A)))


You can then define your range using the MAX of the above:

Name: _test
RefersTo: =OFFSET($A2,0,0,MAX(_lastno, _lasttxt, _lastlogic),1)

Does that help at all, not sure ?


Someone else will have a better approach... I tend to avoid using DNR's myself wherever possible given they're invariably volatile -- that said using Named Constants for things like BIGNUM make a lot of sense :-)

....looks a lot like a Domenic method...

Erm, no, in this instance I actually just put it together -- I've never done so previously but if this is something you've seen Domenic do before then that's all good... Domenic is more than aware as to just how highly I regard his XL techniques !

I like to think that if I demonstrate a method that's to all intents & purposes the creation of another member otherwise not seen that I wouldn't try and pass it off as my own and would mention said member appropriately...

The above still doesn't account for error values mind... should the last value be an error the range would ignore it ... though I'm sure there's a way to find the last error value also...

Wasn't meant to offend...just commented that the style (i.e.using all those Named Formulas is what I often see Domenic solutions consist of) ... that's all

OK, here's my tortured approach, similar to DO/Domenic's.

Suppose we have a table of variable-length data starting with headers starting in D:F with a header in row 6. We want definitions that ensure that adding data to the table (top, bottom, or in between) extends all ranges appropriately to include it, and allows moving the table without hosing the ranges.

Define:

ptrTL Refers to: =$D$6
rgnHdrDown Refers to: =ptrTL:$F$65536
frmEnd Refers to: (any method appropriate to table contents; sometimes I just use the word End if I really want to have other data below the table)

This defines everything from the header row downward. The actual data starts one row below that, so we define:

tbl Refers to: =INDEX(rgnHdrDown, 2, 0):INDEX(rgnHdrDown, frmEnd, 0)

This makes the table start one row below the header, and extend to the last row.

Suppose the table contains values for x, y, z in columns D:F respectively. Then define

ptrX Refers to: =$D$6
ptrY Refers to: =$E$6
ptrZ Refers to: =$F$6

rgnX Refers to: =INDEX(tbl, 0, COLUMNS(ptrTL:ptrX) )
rgnY Refers to: =INDEX(tbl, 0, COLUMNS(ptrTL:ptrY) )
rgnX Refers to: =INDEX(tbl, 0, COLUMNS(ptrTL:ptrZ) )

This seems a little awkward, but it defines the columns in terms of the offsets from the start of the table to their named header cells.
The approach allows rows to be inserted above, and columns to be inserted to the left or the interior of the table, with ranges adjusting automatically.
Suppose you want to apply a function (say, SUM) to a particular column. Use this formula in the same column, anywhere above the data:

=SUM(INDEX(tbl, 0, COLUMNS(ptrTL:Me))

Thanks to both of you. I'll need to print these up and play with them. I appreciate it.
Is OFFSET one of those Volitile formulas like INDIRECT which recalculates everytime there's a change in the worksheet?

OFFSET and DIRECT are both volatile, INDEX is not.