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:

Aladin Akyurek

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

When INDEX is used for reference construction, it becomes volatile...
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
Aladin,
I put a Beep in the Calculate event.
Then I put =ROWS(A1:INDEX(A:A,C1,1)) in D1

When I changed a value in A:A or C1, I got a beep.
When I changed a value in G12, no beep.

Using Index in this fashion can make really big precedent ranges (like A:A), but its not volatile.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Aladin,
I put a Beep in the Calculate event.
Then I put =ROWS(A1:INDEX(A:A,C1,1)) in D1

When I changed a value in A:A or C1, I got a beep.
When I changed a value in G12, no beep.

Using Index in this fashion can make really big precedent ranges (like A:A), but its not volatile.

I think it is. Some people call INDEX in reference construction semi-volatile.

Try to use A1:INDEX(A:A,C1,1) in a closed book. Excel treats this as it would treat an OFFSET version.
 

hershmab

New Member
Joined
Mar 17, 2011
Messages
38
Forgetting volatility (which is of little relevance because of the small size of the worksheet), a different problem arises whenever the first row of the range is moved or deleted: the start address of the defined range becomes #REF! and so does the entire range.

The solution to this is to define the top cell $B$8 as OFFSET($B$7,1,0) - which is more robust as the first 7 rows of the worksheet are so defined as to be effectively unchangeable.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917

ADVERTISEMENT

Instead of A1, you could use INDEX(A:A,1,1) which will return A1 no matter what is inserted or deleted (Except for the whole of column A)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Forgetting volatility (which is of little relevance because of the small size of the worksheet), a different problem arises whenever the first row of the range is moved or deleted: the start address of the defined range becomes #REF! and so does the entire range.

The solution to this is to define the top cell $B$8 as OFFSET($B$7,1,0) - which is more robust as the first 7 rows of the worksheet are so defined as to be effectively unchangeable.

Instead of A1, you could use INDEX(A:A,1,1) which will return A1 no matter what is inserted or deleted (Except for the whole of column A)

Since the original definition has B8 as the cell where the data starts, one would have it 8, unless the header must be included:

=INDEX('To do'!$B:$B,8,1):INDEX('To do'!$B:$B,MATCH(BigNum,'To do'!$B:$B)

The OFFSET equivalent would be:

=OFFSET(INDEX('To Do'!$B:$B,8,1),0,0,MATCH(BigNum,'To Do'!$B:$B)-ROW(INDEX('To Do'!$B:$B,8,1))+1)
 

hershmab

New Member
Joined
Mar 17, 2011
Messages
38
Thanks to everyone who has contributed to my questions. I have enough useful ideas to make sure that I can solve any other related problems.
I have just one other question for Aladin Akyurek:
I understand what is meant by BigNum and BigStr, but my Excel 2010 does not recognize them as functions or globals or names. Are they simply words that I could define as names if I wanted?
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thanks to everyone who has contributed to my questions. I have enough useful ideas to make sure that I can solve any other related problems.
I have just one other question for Aladin Akyurek:
I understand what is meant by BigNum and BigStr, but my Excel 2010 does not recognize them as functions or globals or names. Are they simply words that I could define as names if I wanted?

Activate Formulas | Name Manager.
Define BigNum as referring to:

=9.99999999999999E+307

Repeat the procdure for BigStr which must refer to:

=REPT("z",255)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,346
Latest member
mmoose

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