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!
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.