Variable-sized NAMEd range

hershmab

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

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
36
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
22,784
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
36
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,138
@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
36
@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,138
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?
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top