Beyond Excel: VBA and Database Manipulation

Apr 18 2011   11:47AM GMT

“Can there be Dynamic Ranges for VLOOKUP and Data Validation List for an Invoice?”.



Posted by: Craig Hatmaker
Tags:
excel

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!

Summary:
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.

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • silentrouge
    Can you elaborate more on the aspect of "Neighbor Aware:Tables automatically move other tables as they grow (with some restrictions)".
    Does that mean two adjacent table will grow/shrinks together?






    0 pointsBadges:
    report
  • Craig Hatmaker
    Hi selentrouge,

    It means that if rows are added to table A that is above table B, Excel will move table B down to make room for the new rows in table A.

    It also means that if columns are added to table A that is left of table C, Excel will move table C to the right.

    The restrictions are that Excel will only attempt to add cells directly adjacent to the table that is growing. If adding those cells does not result in moving the ENTIRE table that is beneath (or right), Excel won't allow adding the rows or columns.

    Example 1: Table A has 2 columns. Table B has 2 columns and is 2 rows below Table A. We add a row to Table A. Excel insert 2 cells between A and B which moves both of B's 2 columns. Success!

    Example 2: Table A has 2 columns. Table B has 3 columns. We try to add a row to Table A. Excel attempts to insert two cells between A and B. This would only move 2 of B's columns, not all 3. Excel aborts the insert. Failure.
    1,610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: