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.
Excel Step Charts
Claire Squibb
Really useful article that explains the problem I had today. I was teaching Intermediate Excel and got my learners to make a DV list based on a table to allow for later expansion. All went well until I added the extra item – and the DV list didn’t expand. I expect it was because, on this occasion, I had decided to put the list on a new sheet. How irritating! So I had to add the extra range name step, which just pushed it over the edge of complexity for the group.
Isn’t this behaviour irritating – it’s time DV got to hear about Tables!
Mynda Treacy
Absolutely agree, Claire! We MVPs have been asking for this for years.
DJones
How did you figure out that INDIRECT would work for structured references in Data Validation?
(Rhetorical). Thanks a bunch!
Mynda Treacy
🙂 glad it was helpful!
Chris Wright
Mynda
That was a big help. I used method 1 and it worked like magic
Chris UK
Mynda Treacy
Great to hear, Chris!
Joseph Payne
Turned each of my reference lists into a table (these will be expanded often). In the data validation input box, could find no way to identify the table as the source. I can select the entire column at the top of the reference list, but the dropdown list then also shows the column heading. How do I correct this?
Mynda Treacy
Hi Joseph,
If the data validation list is on the same sheet as the table, you can simply reference the rows (not the header) and the list will automatically grow with the table even though it doesn’t use the Table’s structured references. Try it and you will see.
If your data validation list is on a different sheet to the table, first define a name for the table rows, then use that name in your data validation list.
Mynda
Jafar
Well done! Thanks very much. Work well
Mynda Treacy
Glad it was helpful, Jafar!
Caroline Michael Kopilyo
Very useful information
Mynda Treacy
Great to hear, Caroline!
Dominik
Thanks for the advice. Some comments which you may want to add to the article are below:
Method 1 has the disadvantage that renaming the table that is referred to will not result in updating the INDIRECT reference. As such, the referred Table should get its final name before making the references.
I noticed that Method 2 unfortunately doesn’t work when the Cell to be validated is itself in another table. I see no apparent reason why that is the case, but I couldn’t get it to work.
Mynda Treacy
Hi Dominik,
Regarding method 1, you’re correct, no table names dynamically update this way.
Regarding method 2, I tested it and it worked for me.
Mynda
Chris
These work when adding data to the bottom of the list. How do you get data validation to work when you add a column as I can’t seem to get it to work. In my example I am expanding the table to the right by adding additional months, with data populated below the month name.
I want the data validation to offer me the month names.
Philip Treacy
Hi Chris,
You have to refer to table headers, like: =Table1[#Headers]
To refer to a month, try:
=Offset(Table1[Column1],0,MATCH(“June”,Table1[#Headers],0)-1)
Simon
Thank you so much! A long-standing ‘issue’ with Excel that has puzzled me. No matter… a bit of extra typing never hurt anyone and the INDIRECT solution works a treat.
Arnie
Thank you Mynda, this was really helpful.
I was puzzled that the column names of an area formated as a Table (i.e. the columns names in the Header) couldn’t be used at all, but your trick n°2 worked fine.
I’ll definitely have a look at the other interesting information you are sharing
Arnie
Mynda Treacy
Glad I could help, Arnie 🙂
Rich Malloy
Mynda,
Nice page on Data Validation Lists from Excel Tables. Thank you for posting it.
I have noticed an obscure problem with the third method you posted, i.e., using explicit cell references to refer to the list. If the Excel Table and the Data Validation drop-down menu are on the same worksheet, the menu will indeed grow dynamically as the table grows.
But, if the Excel Table is on a different sheet, then the Data Validation List will not grow. Because I often like to put lookup tables on separate sheets, I usually have to use your Method 2, where I define a name for the Lookup Column in the table.
Mynda Treacy
Hi Rich,
Yes, you’re right. The data validation list referencing a table will only dynamically grow if they are on the same sheet. I’ll add a note about that. The workaround is as you described; give the table a named range and then use that in your data validation list.
Thanks for the reminder.
Mynda
Julian
Really helpful!
Thank you.
filterbank
thanks for the tips… was also going crazy over this point…
Mynda Treacy
Glad it was useful.
Pradeep G
Thanks a lot. Came across these tips after going nuts for an hour.
Mynda Treacy
Glad we could help, Pradeep.
Scott
Thank you for the tip!!! I searched all over the internet about this very problem. You were the only site I could find with the solution!!!
Thank you so very much!
Scott
Mynda Treacy
Glad I could help, Scott 🙂
Stephen
Thanks very much for sharing this info – it was driving me nuts why couldn’t get it to work.
Stephen
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.
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
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
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
Great idea. Done 🙂
Guillermo G.
Thanks!
Lynda
Sweet!
Philip Treacy
You’re welcome 🙂
Robert
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
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
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
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
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
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
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
Awesome! Glad we could help 🙂
Paul Spiteri
Very helpful, congrats on explaining quickly/easily
thanks
Mynda Treacy
Thanks, Paul 🙂 Glad I could help.
Sorin
Hi Mynda,
It is very interested and useful. Thank you.
Best regards,
Sorin.
Mynda Treacy
Thanks, Sorin 🙂
Justin
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
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
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
Cheers, Cyril 🙂
Teddy Fragopoulos
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
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
Great resource. Can’t wait to try it! Thanks for doing simple and clear writing.
Mynda Treacy
Cheers, Brenda 🙂
Bob Watson
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
Cheers, Bob. Thanks for sharing our tutorial.
Kind regards,
Mynda.