Beyond Excel: VBA and Database Manipulation

Feb 24 2011   6:53PM GMT

Solving Dynamic Ranges with Tables/ListObjects



Posted by: Craig Hatmaker
Tags:
database
development
dynamic ranges
excel
ListObjects
Microsoft Excel
ms query
odbc
sql
Tables
tutorial
vba

Dynamic named ranges have been a subject of interest on Excel boards for sometime.  Lots of people want to know what’s the best way to create a named range that expands (or shrinks) according to the data entered, and can be used in formulas like VLOOKUP without change.  For some background on this subject see: Dynamic Ranges – Overcoming Shortcomings.  In that post I promised to talk about what I believe is truly, the one best way to handle this kind of thing, Excel’s Tables.

Tables!

In 2003, Excel introduced something called a “ListObject” also known as a ”Table”.  There are several features packed into Tables, but the main feature of importance in this discussion is their ability to grow or shrink dynamically.  Out of the box, a table is a dynamic named range.  So why agonize over what formula best creates one?

Creating tables is easy, simply select any cell within the desired range of data and press Ctrl-L (in Excel 2007 or 2010 you can also use Ctrl-T).  Excel will probably guess correctly what range you meant but gives you the opportunity to specify exactly what you want.  Whenever your selected cell is in a table Excel adds the Table Tools tab to its ribbon. There you can change the table’s name using the convenient “Table Name:” box in the upper left corner.

Unlike formula approaches, tables don’t mind other tables on a worksheet.  And Tables come with right click menu functions for adding/inserting/deleting rows/columns.  As you add rows or columns, the table automatically encompasses the new data, extends formats and formulas, and when rows are inserted, moves tables below to make room. 

Tables are superior to named ranges in every way except one: they can’t be used as datasources.   That’s easily overcome.  Simply select the range you want to use as a datasource (that can be the entire table, just the data, a column…) and give it a name using the name box in the upper left corner of each worksheet, or using the name manager.  The new name is every bit as dynamic as the best dynamic range formula – without the formula.

It’s Time to Move Ahead

At the time of this blog post, I still meet Excel fans enthusiastic over dynamic ranges.  When asked “why not tables?” I usually get a blank stare.  Some point out tables don’t work with all versions of Excel.  True enough.  But it’s been almost a decade now since XL 2003 came out.  I’ve long since upgraded every user in the company I work for to Office 2003 or higher.  I’ll bet your company has too.  So put your favorite dynamic named range formula in a frame and hang it on the wall with other momentous from your past.  It’s time to move ahead with tables.

 Comment 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

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: