Named Ranges in Excel are pretty cool. They enable things like drop-down lists in your Data Validation. You can define a series of values in a column, give that column a name, and then you can refer to that range by name instead of its coordinates (ie: A1:B5).
One of the frustration with maintaining these lists is that as soon as you add a new value, you have to go back to Formulas > Name Manager and redefine the range to include the new value. To avoid this, you can create what’s called a Dynamic Range, by using a formula instead of a hard-coded set of coordinates. This is most often handled using the OFFSET( ) function as you’ll see below. Googling “Excel dynamic range” will return tons of results that all have variants of:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)
The Basic Formula, Explained
‘OFFSET’ will return a range based on a STARTING POSITION, the ROW OFFSET (that gets added to the starting position), the COLUMN OFFSET (added to the starting position, the HEIGHT (number of rows down) and the WIDTH (number of columns across).
STARTING POSITION is usually the top left cell of your named range
ROW OFFSET is usually 0, since we have defined the starting position of our range
COLUMN OFFSET is also usually 0, for the same reason
HEIGHT is the number of rows to include in the range (here we’re using another formula – more on that below)
WIDTH is the number of columns to include in the range (must be at least 1)
Expanding the Range Dynamically
So clearly, OFFSET by itself doesn’t do that much for us – in fact, if you’re just going to plug static number into OFFSET, there’s actually no use for it – just plug in your range directly and leave it at that. The reason you use OFFSET is because it allows you to substitute any of its parameters (arguments) with some other formula. This is where the ‘dynamic’ part comes in.
The typical dynamic range formula you’ll find on the Interwebs uses COUNT( ) (for numerical columns) or COUNTA( ) for text columns. COUNT and its text cousin COUNTA count the number of non-blank cells. If we count the number of non-blank cells in a row and use that as the HEIGHT parameter of OFFSET, we get a range that starts at the starting coordinate and continues on up to the last cell that contains a text entry. In theory.
In actuality if you read the fine print, what you’ll get is a range of cells that starts at the first cell and continues down for as many cells as were counted by the COUNTA function. If there are blank cells in the middle of your range for some reason, this number will be wrong. That’s because the non-blank cells are not counted, so your range will be shorter than it should, and the last cells in the range will be left off. So be aware of this subtlety.
Read More
