Dynamic Named Ranges

Nearly two years ago, (see my September 15, 2008 post), I wrote about the wisdom of using Named Ranges in Excel. Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

The only problem with a Named Range is that if you have a static cell reference, (e.g. $A$1:$A$20), when you add additional data, you will typically need to go to the Name Manager and Change the reference. What a drag!

Happily, there is an easy solution. Excel allows you to enter a Formula in the Named Range reference. Although there are several formulas that work for this purpose, the following one is one of the most versatile. Let’s suppose that you have you data in Column B, and you do not want to include the header in B1. By using this formula, and assuming you do not have any blank cells, (which is a bad database management practice anyway…), you will include your entire range on a Dynamic basis:

=INDIRECT(CONCATENATE("$B$2:$B$", COUNTA($B:$B)))

As a consequence, more data can be added to your database, and your Named Ranges will always remain Up-to-Date with no muss, no fuss. Wow, that Rocks!