I was reading Contextures post about duplicate entries. In it, she creates a named range that refers to a Table column. That named range will expand and contract as the Table does.
Tables have their own built-in names and they are so handy that I find myself using Tables whenever possible. Here’s me using the table name and an open bracket to get a list of columns while entering a formula.
You may have guessed that I prefix all of my Tables with “tbl”. I also use the singular noun, so it’s tblCustomer and not tblCustomers. One of my favorite uses of Table references is using them in VLOOKUPS. The third argument to VLOOKUP is the column from which to pull. I’ve never been a fan of making that dynamic. Instead, I would hard code the number and change it if the columns change. But with Table references, I find myself making that argument dynamic. It’s a little less error prone, a lot more self-documenting, and while it’s more typing, I don’t have to count columns. An old VLOOKUP of mine might look like
=VLOOKUP(G1133,$A$1:$J$331,2,FALSE)
And now it looks like this
=VLOOKUP(G1133,tblCustomer,MATCH("Carrier",tblCustomer[#Headers],FALSE),FALSE)
Instead of hard coding “2” for the column, I’m hard coding “Carrier”, which is the column name. Obviously that could change, but it will give me an #N/A! rather than pulling wrong information.
The autocomplete feature of the table references makes this easy to enter. I can type “tbl” and get a list of all the tables in my workbook.
Then I can hit the tab key to finish the job. Next, the open bracket gives me all the elements in the table, including #Headers.
But this was supposed to be about naming columns and I got off on this VLOOKUP thing. One of the limitations of Table references is that you can’t use them directly in data validation and conditional formatting. The way around that, as Contextures employs, is to create a named range that refers to the column. I use a convention when naming Table columns.
The name is prefixed with “dv” (for data validation). Then the table name without the “tbl” prefix. Then the column name. The big advantage to me to using this convention is that it’s easier to type the named range in the Data Validation box. There’s no intellisense in the Data Validation box, so you have to remember the named range exactly. Also, when you’re entering a formula in the formula bar, it’s nice to type “dv” and see all of the named ranges that start with that. Or to type “dvCustomer” to see all of the columns in that table that have a named range.
One of the downsides is that the named range can get long. If you have tblCustomer that has a CustomerName column, the named range becomes dvCustomerCustomerName. I think the benefits outweigh the costs.
Leave a comment with your conventions for naming ranges.
@Dick, there’s no intellisense in the data validation dialog box, but you can press F3, to get the Paste Names window, and add a name from that list.
Am I the only one that has performance issues with tables – to the point where whenever I try to use them I end up reverting to dynamic named ranges? I’ve had small tables (20 columns by 100 rows) that hang Excel for 5 minutes when inserting a row. Seriously. Does no-one else experience this?
You can use INDIRECT with the table naming conventions in data validation
@Dick
There is one more level of abstraction that makes the whole thing bullet proof to future changes in column label changes (at least in my experience so far).
Instead of hardcoding “Carrier” into the lookup formula, try clicking on the header label cell – and you will find the following is inserted into the Match function
tblCustomer[[#Headers],[Carrier]]
And if someone changes Carrier to something else, this formula will change automatically – a thing of beauty – except of course it makes the simple lookup look even more obscure) – but I find it reduces errors introduced by users who may not know that header labels should not be changed or that add columns when the column number was a hardcoded.
I use named ranges in a similar fashion – a lot.
Only, I use the period (.) and underscore as separators to make the names easier to read.
Apart from popping up as auto-complete suggestions while writing formulas, prefixing range names like this is very useful while writing VBA code, because it makes the code easier to write, and easy to maintain as well.
Some of the prefixes I use are –
db. for tables that are sub-databases in workbooks where I have already used the special name ‘Database’ for the main data table (Dick uses ‘tbl’)
dv. for data validation lists (the same as Dick)
vl. for vlookup tables (used in formulas)
afcr. for advanced filter criteria ranges (which include the field name + the criteria)
cr. for advanced filter criteria (the criteria text)
fd. for field header names (useful in VBA code for sorting tables)
tf. for boolean value cells
@Charlie, that’s an interesting option ( using [#Headers] ).
But having learnt the hard way, I use data validation to prevent users from making unintended changes to column headers.
I use one of Debra’s custom tricks to do this.
Just select “Custom” from the ‘Allow’ drop-down list, and in the ‘Formula’ box, type =”” (blank).
This prevents ANYTHING from being entered in that cell.
Of course this does not work if a user copy-pastes something into the cell :-/
Brilliant! Thanks Charlie.
My only problem with tables is referring to closed workbooks. I don’t remember the details, but I don’t use them in when I have to refer to the workbook when it’s closed.
I have table with many thousands of rows, but probably not more than a dozen columns. No performance problems for me (AMD A4, 4GB RAM).
I use the same prefix-naming convention, “tbl” and “dv”.
Using Index/Match would solve a lot of the problems you address, e.g.:
=INDEX(tblCustomer[RESULT_COL],MATCH(G1133,tblCustomer[CARRIER],0))
Makes no never mind if you move or rename the columns.
Rob, I have had the experience of extreme slowness when inserting into a table. I had some code that did so and took minutes. When converted back to a range it took seconds. It doesn’t happen much though, and I can’t imagine working without tables now.
Rob,
I have not experienced the slowness with Tables as of yet. Hoping I don’t. I’ve never inserted into a table. I append to the bottom and resize the table.
@Doug,
I use the same approach (although I prefer to have my lookup match in a helper column).
There IS, however, a case when moving or renaming the columns will cause a problem: If you are indexing a table that has been imported by a query.
In the case where the column you index has been moved in the source AND your query does not use the option “Preserve column sort/filter/layout” in the query External Data Properties (I also use the overwrite option for good measure)then the index reference will be moved on query refresh. Excel seems to do the update on column index in this case, not column name, the way it is done when you move or rename a column manually.
But why would anyone still be using VLOOKUP rather than INDEX/MATCH?
Probably the same effect.
After changing the table the validation list has been changed too.
Sub M_snb()Cells(24, 1).Validation.Add 3, , , "=" & [t_customer[account]].Address
End Sub
I prefer underscores in names of any kind to prevent any interfering with reserved names.
I find myself creating names for tables that I use in VBA projects, so that if i change the name of the table later on (or if I allow users to change the name), the VBA code doesn’t error out. Particularly during development, when I often change table names to something more descriptive.
Probably JKP’s Name Manager would render this extra step obsolete because I could use it to replace all instances of the old name in VBA with the new one. But I can’t load that add-in on my work pc.
One of the limitations of Table references is that you can’t use them directly in data validation and conditional formatting.
I doubt that. I was able to select a table’s column in a validation rule.
After adding some new records in the table, the validation list has been expanded too.
So I don’t see any need for redundant named ranges.
@snb
Have you tried it when the table is on a different sheet than the data validation??
NetSales = tblCustomerSales[[1],[Dollars]] – tblCustomerSales[[2],[Dollars]]
Some related to this topic: Why didn’t MSFT extend the referencing a bit to support specific row references ?
If they had done that, now they would now have a crude FML (Financial Modelling Language) built into Excel.
@AlexJ
I didn’t because Dick made a general statement about validation e.g.
A lot of validation can be performed in the same worksheet.
But it’s true it’s not working in different worksheets.
Yeah, good point. It does work on the same sheet, or so I assume. One of my other conventions that I didn’t mention (but should have, because it’s a big one), is that when I have a Table on sheet, I have nothing else on the sheet. I may break that rule some time in the future, but as of now, nothing else goes on a sheet that has a table on it.
@Dick
I follow the same approach, except I mght have calculation support constants on the same sheet as the table. These are used to calculate additional table columns (assuming I’ve queried the basic table data into the sheet from another source).
BTW – Contextures had a post within the last few months about Data Validations source from tables (I think using INDIRECT with the table coordinates), however I can’t locate it. This might contribute to the discussion content. Maybe Debra could post a link for us?
I think there’s enough space in a worksheet to house several tables as well as cells with validation rules.
You might distinguish between tables containing validation items and tables containing (‘database’) data.
The curious thing is, that charts can dynamically refer to tables in a different sheet.
So I have no idea what is the rationale for the restriction to validation and conditional formatting.
I like the use of match() in the vlookup() formula, but I make more use of columns() because I sometimes need to use a column other than the first one to do my lookup. The resultant formula makes it quite clear which field I am using for the lookup, e.g. in the table “Time_Entries”, I want to look up the value in the “Code” column to find the value for “Client” in the “Code_Entries” table, so my value for the columns value in my vlookup() would be:-
COLUMNS(Codes_Entries[[#Headers],[Code]:[Client]])
and the full lookup formula would be:-
VLOOKUP(Time_Entries[[#This Row],[Code]],Codes_Entries,COLUMNS(Codes_Entries[[#Headers],[Code]:[Client]]),FALSE)
This translate as “Take the Code value in This Row and look up that Code in the Codes_Entries table and give me back the Client.”