This question was posed and answered by “Mr. Excel” and “Excel is Fun” in a recent YouTube post by the same title. Their solutions work well. I’m going to answer with what I believe is a better approach. It uses Tables. Tables have some great characteristics:
|Dynamic||Tables grow/shrink as rows/columns are add/deleted.|
|Nieghbor Aware||Tables automatically move other tables as they grow (with some restrictions)|
|Structured References||In Excel 2007 and higher (XL’07+), tables create special names so we can address a table’s column by its heading. Ex: =Table[Column]|
|Stylish||Table Styles (in XL’07+) dramatically improve data formatting.|
|Links||They can link to external data.|
|Duplicate Removal||XL’07+ includes a menu option to remove duplicate rows.|
Creating tables is easy. If you have a range that contains data, simply select any cell in the range and use the keyboard shortcut: CTRL-L (in XL2007 and later you can also use the more intuitive CTRL-T). Once a table has been created, you can change its name by selecting any cell in the table and using the menu tab Table Tools. The Table Name input box is on the left in the Properties grouping.
Tables have some drawbacks.
|Not Supported Prior to XL’03||So if you’re using old versions of Excel, use the “Mr.Excel” or “Excel is Fun” approach – or upgrade and start using Excel’s Tables.|
|External Data Source Restriction||MS Query and Data Validation can’t see tables without a Named Range Wrapper.|
A Named Range Wrapper (NRW) is simply a name assigned to a range. To create an NRW for a table just select the table’s entire range and assign a name by typing it into Excel’s Name Box or using the menu path Formulas > Define Name. If you are working with a .xls, you’re done. But if you’re working with a any of the new formats (.xlsx, .xlsm) you must change the name’s Refers to: reference from a table reference to A1 notation. That’s it. The new range is every bit as dynamic as the best dynamic named range formula – without the formula.
“Can there be Dynamic Ranges for VLOOKUP?”
In XL’07+ VLOOKUP works with tables already. If you have a table called “Products” you can use it in VLOOKUP like this:
=VLOOKUP(“ABC”, Products, 2, 0)
Where “ABC” is the value in the first column of Products you’re searching for. In XL’03 VLOOKUP needs an NRW. Once the NRW is created, we use it as we did above.
“Can there be Dynamic Ranges for Data Validation?”
Data Validation does not recognize tables but we can still use them. We can either apply a “Named Range Wrapper” over the table and use the name in Data Validation or we can exploit a quirk of using tables.
QUIRK! Any reference placed over a table becomes dynamic, even absolute cell addresses.
So if we have a table in cells $A$4:$C$10 and we want our Data Validation rule to use the first column’s values (excluding the header) as a list, we can put =$A$5:$A$10 in Data Validation’s Source: box. Now if we add entries to our table, the Source box’s values will change automagically! How cool is that!
Clearly, tables are far simpler than the “Mr.Excel” and “Excel is Fun” approaches (both good approaches, both compatible with older XL, neither requiring VBA). Note that we will never have to worry about other tables on the worksheet like we would with the traditional “COUNT” approaches. We don’t have to worry about the table’s location – ever. We don’t have to worry about blanks, or numeric data, or character data or any combinations thereof that can throw off COUNT methods.