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!
Please Share
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.
Stephen says
Thanks very much for sharing this info – it was driving me nuts why couldn’t get it to work.
Stephen says
As Mynda noted, you can drop the column reference from the formula – so that in your example:
=indirect(“table1[team]”)
can be shortened to:
=indirect(“table1”)
Obviously not something you’d want to do if your “lookup” table has more than one column though!
Alexi M. says
Great info. I got method’s 1 & 2 to work right away. I struggled with method 3, naming the data only range of the table. Adding new rows didn’t appear in the lookup. I ran across this slight variation on method 3 that worked for me:
– Create the lookup list (including a header)
– BEFORE formatting as a table, name the explicit data only range A2:A4 (leaving A1, the heading, out)
– THEN format the list as a table
– NOW adding & removing rows are reflected in the lookup (because the explicitly named range gets updated with the changes)
Mynda Treacy says
Hi Alexi,
I wonder if you included the Table header when you tried method 3. It works for me everytime when I don’t include the table header.
Mynda
singaravelu s says
Can you mark the article with Method 1, Method 2, Method 3, etc. instead of simple numeric values. I really mistook it for Step 1, Step 2, Step 3, etc.
Mynda Treacy says
Great idea. Done 🙂
Guillermo G. says
Thanks!
Lynda says
Sweet!
Philip Treacy says
You’re welcome 🙂
Robert says
Hi,
I have a table on one sheet and it’s connected to another sheet as a data validation pull-down menu. I used the “indirect” option. The table is a list of alphabetized names (last,first). When I add a new name to the table and re-sort it by last name (to keep things alphabetized), any data validation cell that was based on the original alphabetized table is changed by the new alphabetical order. How do I get it so that and updates to the table won’t change what was previously selected on the other sheet so that we can continue to use this spreadsheet as we add and delete new people from the team?
Catalin Bombea says
Hi Robert,
You have to prepare an example file with your data structure, it’s hard to visualize what you did. Please open a new ticket on our Help Desk, I will help you find a solution for this.
Cheers,
Catalin
Steve says
I’m still using Excel 2010; using the table name in the formula works well if there is only one column in the table, but when I have multiple columns in my table and there are entries added to only one column it works for that column, but subsequent additions to other columns are not recognized. Do you have a workaround for this?
Mynda Treacy says
Hi Steve,
If your Table has more than one column then you can reference the specific column by using a reference like this:
=Table1[column_name]
The column name is surrounded by square brackets.
I hope that helps.
Mynda
evillama says
Thanks for the tip. Definitely helped me out.
Is there a well defined “reason” for not being able to use a table name directly in the Source field for Data Validation? I get how INDIRECT ends up working, but still unsure why this has to be done…
ENV
Catalin Bombea says
Hi,
You don’t have to use INDIRECT or a defined name for your data validation list. When you create the validation list, instead of typing the table name and column name, try to use the mouse to point to that table column. You will notice that excel will insert the range reference, like: A1:A12, instead of the column name. Now, if you add new rows to your table, you will be surprised to see that the new rows do show up in your validation list, even if you did not used INDIRECT, or a defined name to refer to your table column!
Excel will update the range that refers to a table!
If you want to use the validation list in other sheets than the source table sheet, the only thing you should be aware of is that this works only in excel versions from 2010 up… Excel 2007 and lower versions are not accepting references to other sheets.
Same thing happens in Conditional Formatting rules, you cannot use table names there, Excel will convert them to the equivalent range, but
it will update the reference when the table size increases!
Using names is the best choice though, this is what I recommend, because sometimes Excel fails to update table references, especially in tables or pivot tables conditional formattings.
Cheers,
Catalin
Allen says
Brilliant! I must have last search for a solution in 2012, before you published this 🙂
This will simplify so many of my workbooks. Thanks. You just got another subscriber.
Cheers,
Mitch
Mynda Treacy says
Awesome! Glad we could help 🙂
Paul Spiteri says
Very helpful, congrats on explaining quickly/easily
thanks
Mynda Treacy says
Thanks, Paul 🙂 Glad I could help.
Sorin says
Hi Mynda,
It is very interested and useful. Thank you.
Best regards,
Sorin.
Mynda Treacy says
Thanks, Sorin 🙂
Justin says
double naming each of your tables sure makes the name manager busy when you use a lot of tables, not to mention when choosing them from the popup list when you’re writing formulas.
Have you run into performance problems with indirect because it’s volatile or something? What the advantage of method 2 over method 1? I always just used method 1 with indirect.
Carlo Estopia says
Hi Justin,
As far as question number 1, I haven’t run into performance problems yet regarding indirect yet.
Regarding question number 2, I think method 2 is a lot organized and quicker for Excel to process.
Cheers,
CarloE
Cyril Seguenot says
Hi Mynda,
Thank you very much for this trick. I often use tables and this is a question I am often asked. Now, I have a relevant response 🙂
Mynda Treacy says
Cheers, Cyril 🙂
Teddy Fragopoulos says
Does this require that the table resides on the same tab as the validation area? This has been an undesirable restriction in using an array.
Mynda Treacy says
Hi Teddy,
No, you can put your table on any worksheet and use the INDIRECT function to reference it in a data validation list.
Kind regards,
Mynda.
Brenda says
Great resource. Can’t wait to try it! Thanks for doing simple and clear writing.
Mynda Treacy says
Cheers, Brenda 🙂
Bob Watson says
Tables are a great, though underutilized, feature of Excel; and Data Validation is an essential part of building robust and reliable spreadsheets. Using the two features together like this is a nice combination.
Because we like this article, we’ve added it to our collection of useful and interesting spreadsheet-related articles from the web at http://www.i-nth.com/resources/connexion
Cheers,
Bob.
Mynda Treacy says
Cheers, Bob. Thanks for sharing our tutorial.
Kind regards,
Mynda.