Posted by: Craig Hatmaker
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?”
Tables need the NRW to be used in Data Validation – even in XL’07 and XL’10.
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.