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: