|
Back to Excel Tips Index
The key to creating a dynamic range
is to determine the last used row or column. Work through the following to
learn how to determine the last used row and use it in a formula.
Determine
Last Used Row:
(If the column is Numeric)
Create a column of numbers, for
example, in B1 thru B10 type 11,12, 13...20 and in C1 type the following
formula:
ex.
=INDEX(B:B, MATCH(9.99999999999999E+307,B:B))
The result should be 20. But
what you don't see is that the INDEX function also returns the cell Address
which can be used in a formula.
(If the column is Alpha)
Create a column of letters, for
example, in B1 thru B10 type A, B, C,...J and in C1 type the following formula:
ex.
=INDEX(B:B,MATCH(REPT("z",255),B:B))
The result should be J. But
what you don't see is that the INDEX function also returns the cell Address
which can be used in a formula.
Using Last Used Row in a Formula:
ex.
=SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,B:B)))
or
ex.
=SUM(A1: INDEX(A:A,MATCH(REPT(“z”,255),B:B)))
Note:
This technique is important for faster formula calculation, as in when
performing multi-conditional Counts and Sums because you want to use the
smallest range possible. Also, aids in dynamic spreadsheet design because
the ranges don’t need to be manually altered.
Back to Excel Tips Index |
