Variable-sized NAMEd range

[hershmab]

I have a column-vector that starts at a fixed row (NOT Row 1) and continues down to the last-used cell in that column. The number of used rows varies from time to time, getting either bigger or smaller according to needs. I have defined a Name for that vector and the name is used in several formulas.

My problem is that the Named area changes automatically but does not follow the principle of referring to all and only the used area. This puts out all the affected formulas and forces me to redefine the name whenever it happens.

If I define the end of the vector by

1. an absolute row address, the Name should never change but does;
2. a relative row address, it should change does does not do so predictably.

It would not matter if the name referred to empty rows as the formulas take care of that.


So the question is: how can I define the name so that it always refers to the currently-used part of the column, or at the very least to the open-ended/half-closed part?

 

[BiocideJ]

I use something like the following as the address for dynamic named ranges.

You didn't give much specific detail to your actual fixed starting point so I just made up $L$7. Please replace that cell with your actual starting position cell.
Also, make sure the column for the reference to cell L1048576 matches the column for your starting position and the Sheet1 is the actual sheet you are using.

=Sheet1!$L$7:OFFSET(Sheet1!$L$7,COUNTA(Sheet1!$L$7:$L$1048576)-1,0)

 

[hershmab]

I use something like the following as the address for dynamic named ranges.

You didn't give much specific detail to your actual fixed starting point so I just made up $L$7. Please replace that cell with your actual starting position cell.
Also, make sure the column for the reference to cell L1048576 matches the column for your starting position and the Sheet1 is the actual sheet you are using.

=Sheet1!$L$7:OFFSET(Sheet1!$L$7,COUNTA(Sheet1!$L$7:$L$1048576)-1,0)

Many thanks, that is exactly what I have been seeking for several years.

 

[mikerickson]

Or you could use the non-volatile

Sheet1!$L$7:INDEX(Sheet1$L:$L, Match("zzzzzz",Sheet1!$L:$L), 1)

If you expect the last entry to be text

 

[hershmab]

Or you could use the non-volatile

Sheet1!$L$7:INDEX(Sheet1$L:$L, Match("zzzzzz",Sheet1!$L:$L), 1)

If you expect the last entry to be text

I am afraid I cannot understand just what this expression should do.

When I try it out in a formula with the relevant addresses replaced, the MATCH function generates either 1 or #N/A, depending on the value of the 3rd Match argument (unspecified!), whereas it should generate the row number of the bottom-most non-empty cell in the column.

 

[mikerickson]

Do the sheet names and column L match at all three places.

 

[Aladin Akyurek]

@hershmab

Care to specify the current range or ranges that are involve along with the kind of data they house?

 

[hershmab]

@hershmab

Care to specify the current range or ranges that are involve along with the kind of data they house?

The actual range contains date values. If I allow for this in my version of mikerickson's formula, his formula works just as well as BiocideJ's - i.e perfectly.

How is it that one formula is [more] volatile than the other?

 

[BiocideJ]

His is less volatile because you don't have to change it to account for the start cell (L7) moving up or down (or even know where it is at all).
Also, I suspect that a MATCH function on an entire column is going to be faster than a COUNTA formula as well.

EDIT: Actually, after I posted this I rethought what I said and I think the volatility he was referring to was that OFFSET has to recalculate every time an Excel cell changes whereas INDEX/MATCH formulas do not.

 

[Aladin Akyurek]

The actual range contains date values. If I allow for this in my version of mikerickson's formula, his formula works just as well as BiocideJ's - i.e perfectly.

How is it that one formula is [more] volatile than the other?

But what is your current range and your current sheet?