Variable-sized NAMEd range

hershmab

New Member
Joined
Mar 17, 2011
Messages
38
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?
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

New Member
Joined
Mar 17, 2011
Messages
38
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
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

New Member
Joined
Mar 17, 2011
Messages
38

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

@hershmab

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

hershmab

New Member
Joined
Mar 17, 2011
Messages
38
@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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top