Dynamic named ranges – the basics
Whoah nelly, what’s a named range first of all let alone a dynamic one?
A named range is a shorthand or alias for a set of cells in Excel. These can be created easily by simply selecting one or more cells and using the name box to give it whatever name you feel relevant. This alias can then be used in formula to make something much more insightful like =A1_VAT as opposed to =A1_0.2
For more info on these, I can highly recommend the Contextures blog and video:
Ok, so what’s a dynamic named range?
A dynamic named range is one that can grow or shrink dependant how much data there is, instead of a fixed size.
What’s the point?
This allows you to build pivot table connections that always select the relevant amount of data, it allows you create dropdown list for validation that can be easily updated, and it allows you to produce cleaner formula. There are many other uses including producing charts with the last X entries showing only.
So, how do I do it?
You need two functions COUNTA and OFFSET
- COUNTA (as opposed to just COUNT which will only count numbers) will work out how many rows (or columns) need to be included in the range
- OFFSET will build the range of cells needed based on a starting cell and some info how the range needs to be built
A dynamic named range formula looks like
=OFFSET(Sheet1!$A$1, 0,0, COUNTA(Sheet1!$A$1:$A$1000))
The start point
How many rows and columns it needs to move
How many rows it needs to extend – no need to use $A:$A since this uses unnecessary processing power
Included to allow the formula to be hardcoded to a specific range in a sheet