I’ve set up a table with some team names that I want to use in a Data Validation list.
The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list.
My table’s name is Table1, as you can see here in the name box:
Now, if you’ve ever tried to reference an Excel Table as your Data Validation lists source like this:
Then you probably got the error:
“The formula you typed contains and error”.
Here are three ways to fix this:
Method 1: Use the INDIRECT function with the tables structured references like this:
Method 2: Give your Table another name in the name manager. In this example my table is in cells A2:A7 and is called Table1. In the name manager I’ve set up a new named range called Team_Table and referenced it to the Team column (and only column in this case) of Table1:
Now I can set up my Data Validation list like this:
No need to use INDIRECT, and it will still dynamically update as new data is added or removed from the table just like the first example.
Method 3: Simply reference the cells in the table:
Then when you add items to the bottom of the Table your Data Validation List source automatically updates*, almost like magic. No named range, no Structured references:
It's a shame you can't just use the Table Structured Reference as your Data Validation list source. I suspect this is a bug/oversight. Unfortunately I'm not aware of any plans to fix it and it's still like this in Excel 2016!
*Note: this requires the Table to be on the same sheet as the data validation list. If not, use method 2.