Following on from Creating Named Ranges in Excel®, this blog post describes how to create dynamic named ranges.
If you’re not familar with named ranges, I’d really suggest google searching “named ranges in excel” or read the post linked to above.
As mentioned many times on this site, named ranges are really useful, however there are situations where the data you need to reference is not in a static cell range – that is, the cell range is dynamic. To combat this, we can use the OFFSET and COUNTA functions.
First, let’s look at COUNTA – it is a function that most Excel users would be familiar with. The COUNT function returns the number of numbers in a cell range. For instance:
=COUNT($A$1:$A$10) would equal 10
=COUNTA($A$1:$A$10) would equal 10
=COUNT($B$1:$B$10) would equal 0
=COUNTA($B$1:$B$10) would equal 10
Basically, COUNTA counts the number of cells in a range that have at least some value – not just numbers.
=COUNTA($B$1:$B$10) would now equal 5
It is important to understand that this as it will be instrumental in determining the height and width of our dynamic named range.
The next function to investigate is the OFFSET function. The definition of OFFSET is:
For those of you who don’t know, the square brackets (i.e. “[" and "]“) surrounding the parameters of a function indicate that it is optional. This is fairly standard coding convention.
To break this down:
- reference corresponds to the starting cell position
- row corresponds to the number of rows we are going to move downwards, relative to the starting cell (or upwards, if we use a negative value.)
- column corresponds to the number of columns we are going to move to the right, relative to the starting cell (or to the left, if we use a negative value.)
- [height] corresponds to the height (or number of rows) of the range starting at the adjusted position.
- [width] corresponds to the width (or number of columns) of the range starting at the adjusted position.
An example will almost certainly make this easier…
We previously defined the three (3) named ranges, “Origins”, “Destinations” and “Distances” defined thusly:
But by using OFFSET and COUNTA we can update our definitions to by dynamic:
- For Origins, =$A$2:$A$4 becomes =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
We start at cell $A$1, we move down 1 cell, across 0 cells to reach our starting position (i.e. $A$2 – you could just as easily have had $A$2,0,0 as your first three (3) parameters.
Now, this is the trickiest (not, tricky, but trickiest) part. We specify a height of COUNTA($A:$A)-1. What does this mean? Well, there are four (4) cells with values in all of Column A ($A:$A) in this example, but we don’t want to include the heading as it doesn’t constitute an “Origin”. We simply subtract one (1) to ignore it and what we have left over is the height. The final parameter value of 1 simply indicates that the dynamic named range of “Origins” is one (1) column wide.
- For Destinations, =$B$1:$D$! the same premise holds. This becomes =OFFSET($A$1,0,1,1,COUNTA($1:$1)-1)
We start at cell $A$1, we move down 0 cells, across 1 cell to reach our starting position (i.e. $B$1 – you could just as easily have had $B$1,0,0 as your first three (3) parameters.
Now the (not so tricky anymore) tricky part; we specify a height of 1 and a width of COUNTA($1:$1)-1. This simply counts up all the values in Row 1, subtracts to row heading in $A$1 and sets the dynamic named range of “Destinations”.
Now that we’ve done these, we can do something particularly fancy to define the “Distances”. This may be a slight point of difference to other tutorials on this topic. The same follows for the other named ranges.
- For Distances, =$B$2:$D$4 becomes =OFFSET($A$1,1,1,COUNTA(Origins),COUNTA(Destinations))
How cool is that! Straight away you may have noticed something special about this definition, but we’ll go through it parameter by parameter, just for completeness (because that’s the kind of guy I am).
We start at cell $A$1, we move down 1 cell, across 1 cell to reach our starting position (i.e. $B$2 – you could just as easily have had $B$2,0,0 as your first three (3) parameters.
Now the (not so tricky anymore) tricky part; we specify a height of COUNTA(Origins) and a width of COUNTA(Destinations). We had already done the hard work defining our row and column “headings” that we can just count them up! We are using dynamic named ranges in another of our dynamic named ranges.
I must admit, I used to use the formula:
Now, while that also works, it isn’t as consise and doesn’t demonstrate what we are trying to define with our named range.
With these definitions, adding more rows and columns will simply increase the COUNTA($A:$A) and COUNTA($1:$1) counts, thus dynamically expanding the range defined using the OFFSET function.
There are certainly aspects of the functions that I have omitted. I’d welcome any comments or criticism, so please feel free to add any comments. If you require further clarification on any part of this article, please leave a comment, or you can email me at: firstname.lastname@example.org
Ryan Kirgan is from Sydney, Australia.