Thursday, July 22, 2010
Dynamic Named Ranges
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:
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!