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?

 

[Aladin Akyurek]

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...

 

[hershmab]

But what is your current range and your current sheet?

The current formula is
='To do'!$B$8:INDEX('To do'!$B:$B, MATCH(999999,'To do'!$B:$B), 1)
i.e. the sheet is 'To do', the range is B8 onwards (currently B8:B40)

 

[Aladin Akyurek]

The current formula is
='To do'!$B$8:INDEX('To do'!$B:$B, MATCH(999999,'To do'!$B:$B), 1)
i.e. the sheet is 'To do', the range is B8 onwards (currently B8:B40)

BigNum = 9.99999999999999E+307, used with numeric ranges/references;
BigStr = REPT("z",255), used with text ranges/references.

See: http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

So:

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

 

[mikerickson]

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]

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]

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]

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]

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]

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?
​

 

[Aladin Akyurek]

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)