How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are two approaches, the INDIRECT function or a named range.
The INDIRECT function is a volatile function meaning it recalculates every time Excel recalculates which is not the case with most Excel functions. If used a lot the INDIRECT function may slow down your workbook calculations considerably, be careful.
It is enough to calculate a formula once, there is no need to calculate all cells every time Excel recalculates. This makes your worksheets faster and more efficient, however, this is not the case with a handful of functions and the INDIRECT function is one of them.
Another downside with the INDIRECT function apart from being volatile is that the Excel Table name is "hardcoded" into the formula. The formula will stop working if you change the Excel Table name.
Table of Contents
- Reference Excel Table headers
- Reference an Excel Table using a named range
- Reference Excel Table column
- Reference Excel Table row
- Reference an Excel Table in a Conditional Formatting formula
How to populate a drop-down list with Excel Table headers?
The image above shows a drop-down list populated with Excel Table header values, this formula allows you to use Excel table headers as values in a drop-down list.
You can also create a named range and reference the headers there.
- Go to tab "Formulas" on the ribbon.
- Press with left mouse button on "Name Manager" button to open the "Name Manager" dialog box.
- Press with left mouse button on the "New" button.
- Type the reference, in this case: =Table1[#Headers]
- Press with left mouse button on OK button.
- Press with left mouse button on Close button.
Now use the named range name Headers in the Data Validation dialog box.
Watch this video to learn more
How to populate a drop-down list with values from an Excel Table column?
The easiest way is to use the INDIRECT function to create a reference to an Excel Table column. References pointing to an Excel Table are called "structured references", they are different from regular cell references.
Cell B15 contains a drop-down list with this formula:
How to populate a drop-down list with values from an Excel Table row?
The INDEX function is capable of extracting values in a single row from a cell range, combine it with the INDIRECT function and a reference to the Excel Table, please see formula below.
Reference a table row in a drop down list, cell B15:
Animated image
The animated image above demonstrates the drop-down lists, how they work and what they link to.
How to reference an Excel Table in a Conditional Formatting formula?
Simply add the INDIRECT function to each Excel Table reference in your formula, please see formula below.
Conditional formatting formula, cell range A13:C22:
The Conditional Formatting formula highlights a row if a record in cell range A13:C22 is equal to at least one record in the Excel defined Table [A1:C11]
Get the Excel file
Reference-a-table-in-a-data-validation-list-or-conditional-formatting-formula.xlsx
Weekly Blog EMAIL
[newsletter_signup_form id=1]Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated []
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column []
Prevent duplicate records in a worksheet
This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to []
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We []
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using []
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. []
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We []
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using []
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. []
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers []
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in []
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD []
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column []
Comments [32]
32 Responses to How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
- Ralf says:December 6, 2012 at 10:35 am
How would you replace [First Name] with the value of the chose header field?
it only seems to work when specifically selecting the Header name like [First Name]
id like it to be more dynamic.
if i chose Last Name in the headers dropdown, i would like to see Onlyl the Last Name Choices in the next dropdown box.
Reply- Oscar says:December 7, 2012 at 12:35 pm
Ralf,
read this post:
Create dependent drop down lists containing unique distinct values
- Chris Hills says:June 27, 2013 at 7:42 am
Thank you! This tip has saved me a whole lot of effort.
Reply- Oscar says:July 1, 2013 at 12:13 pm
Chris Hills,
Thank you for commenting!
- nandor.dudas says:September 10, 2013 at 12:02 pm
Hello,
i have a question.
It's possible "data validation" in collaboration mode, because is not work for me [2002, 2007]?
Therefore i think, then i creating a macro, that paste a "drop down list" into cells.
Thank You for Your answerReply- Oscar says:September 10, 2013 at 12:27 pm
It's possible "data validation" in collaboration mode, because is not work for me [2002, 2007]?
No!
Avoiding Shared Workbooks in Excel
- nandor.dudas says:September 12, 2013 at 6:57 am
Thank You
- Jake says:November 8, 2013 at 9:50 pm
This is great - love this overall approach as a framework for data validation as opposed to the old way of naming ranges but then having to use OFFSET + COUNT so that the ranges could expand/contract based on data in them.
My only question is, why does Excel require the INDIRECT when referring to these table properties during data validation in the first place? If I am typing a normal formula, I can just get refer directly to Table[Column1] as in =SUM[Table[Column1]. BUT, if I want to do data validation, I can't type in =Table[Column1] but have to instead wrap that in quotes and then use INDIRECT.
Why?
Reply- Oscar says:November 11, 2013 at 9:14 am
Jake,
My only question is, why does Excel require the INDIRECT when referring to these table properties during data validation in the first place? If I am typing a normal formula, I can just get refer directly to Table[Column1] as in =SUM[Table[Column1]. BUT, if I want to do data validation, I can't type in =Table[Column1] but have to instead wrap that in quotes and then use INDIRECT.
Only Microsoft can answer your question but my guess is that they have not thought this through.
- Trevor says:November 13, 2013 at 12:54 am
Thanks for this ... just what I needed. Just hit the problem of a] not being able to use a table in Data Validation and b] not being able to use a table in Conditional Formatting. And here's the answer to both my problems in one go! Excellent.
I had worked around it by defining a Named Range and referring that to the data area of the table. I thought that was a neat little get out of jail card. *But* it seemed a little tedious having to use two references for one range.
So, thank you very much for your help. Trevor
Reply- Oscar says:November 15, 2013 at 3:51 pm
Trevor,
thank you for commenting!
- Trevor says:November 15, 2013 at 5:35 pm
Oscar, you're really welcome. You would not believe how useful this has been in a current project. Hours of fun :]
So much easier than creating Dynamic Named Ranges [although I use INDEX/COUNTA rather than OFFSET]
Cheers and thanks again.
Reply - Thomas says:January 3, 2014 at 6:22 pm
Thank You!!!!!
Reply - Tony says:January 6, 2014 at 6:30 pm
Is there a way of using the table reference as you have defined above but allowing for the fact that there may be blanks in one or more rows.
For instance in my table I might have 200 rows, but for my dropdown I only want to collect the non-blank row values. At the moment I end up with a lot of blanks in my dropwdown because of this issue. Haven't yet found a way round it..although it may be obvious!
Many thanks
TonyReply - Tony says:January 6, 2014 at 8:35 pm
The only way I have so far found to do it is using INDIRECT as follows:
=INDIRECT["Import!$X$2:$X$" & COUNTA[RawData[Currencies]]-COUNTBLANK[RawData[Currencies]]]
The COUNTA[...] - COUNTBLANK[..]
is required it would seem as COUNTA on its own will count cells with a formula in them even though the value is blank [""]. COUNTBLANK counts blank cells even if there is a formula.
Tony
Reply- Oscar says:January 10, 2014 at 11:27 am
Tony,
Thank you for posting a workaround.
- Chris says:May 1, 2014 at 11:59 am
Is there a way, other than building a calculated cell and pointing to that, of using concatenated table cells in the data validation list, using indirect? I've tried it, and it comes back with an error:
=INDIRECT["BReq_T[BR'#]"] & " - " & INDIRECT["BReq_T[BR Scope]"] & ": " & INDIRECT["BReq_T[BR Details]"]
In short - I have two sheets - BReq [contains a list of Business Requirements] and FReq [contains a list of Functional Requirements].
Each FR is to be mapped to a number of BRs - but as the list contains BRs from across a number of phases, we need to ensure the user can determine that they are mapping the right BR, but without restricting the list - hence the concatenated list.
I can build a lookup list, and point data validation at that, but it is not ideal - so I thought the indirect method would work nicely, but alas it appears not in my case!
Any help would be appreciated - can send a sample workbook if needed.
Thank you!
Reply- Chris says:May 15, 2014 at 6:33 pm
Hi Oscar - I managed to sort this out using VBA and the SPLIT function - thanks.
Can you use the '=INDIRECT["PivotTable[Row FieldName]"]' approach for validation when your data source is a Pivot table? It also seems to resolve in an error!
Thanks, Chris
- data validation from table column Excel - Arunkumar Navaneethan says:January 20, 2015 at 9:58 pm
[] data validation from table column []
Reply - Roro says:May 28, 2015 at 12:25 pm
Thank you !
Reply - Alicia says:June 15, 2015 at 7:50 pm
I have a table that has a list of employees and the columns include name, location, address, telephone #, etc... On another worksheet I have a list which lists each location, pick up person [must be employee] who will pick up paperwork that week. I only need 1 person from each location. Right now I have that as a data validation using the indirect function which lists all of the employees for me to choose from. I would like a data validation formula that looks at the employee info sheet and only shows me the ones that are from that location when I press with left mouse button on it.
Thanks for any advice!
Reply - Peter says:July 9, 2015 at 5:56 pm
good article... simply explained.
I'll come back for more tips...
Reply - Alex says:September 4, 2015 at 9:49 pm
This is an older post - so hoping someone will read this.
Indirects are powerful functions, but they come at a cost. Being a volatile function, they are always recalculated [and dependencies] whenever Excel needs to recalculate.
My question: what are the performance implications of using this method on a large workbook where efficiency is a must?
Reply - Daniel says:December 18, 2015 at 12:28 pm
Hi, what if I have several people under the same company? For instance, I would like to choose a company first, let's say Trans Regional Airlines. But in Trans Regional Airlines, I usually talk to five people. I want to be able to pick one of the five people from the company through a list too. How can I do that?
Reply - Grant says:December 29, 2015 at 10:23 pm
I forget about INDIRECT. Great post.
Reply - DF says:March 30, 2016 at 5:59 pm
Fantastic post, I know Named Ranges, I know Data Validation, I know Tables.
I know using a Named Range with spaces essentially breaks the Validation Part of a Drop Down.
However I didn't know to use a combination of Tables with Drop Down & Data Validation to make Excel actually fully usable!!
Thank you very much!!!
Reply - Imran Nazir says:August 15, 2016 at 8:52 am
Hello Oscar
Thank you for the information, it was helpful. Can you please tell if it is possible to select multiple values using a table for data validation? Like in your example in a data-validated column is it possible to have values: Donnica, Arvilla
Reply - giedrius says:November 11, 2016 at 7:19 am
Excelent article. Is there a way to refer only to filtered table values in the list? It now seems that all the table field values appear in the drop down list regardless if table is filtered or not.
Reply- Oscar says:November 16, 2016 at 3:40 pm
giedrius
This array formula lists all filtered values in column [First Name]:
=INDEX[Table1[First Name], SMALL[IF[SUBTOTAL[3, OFFSET[Table1[First Name], MATCH[ROW[Table1[First Name]], ROW[Table1[First Name]]]-1, 0, 1]], MATCH[ROW[Table1[First Name]], ROW[Table1[First Name]]],""],ROW[A1]]]
However this won't work in a drop down list, only the first value in the array is shown.
- Manuel says:March 31, 2017 at 9:29 pm
Thank you very much, Oscar. A real time saver.
It is valuable information, indeed.
Reply - Daniel Mikhailov says:October 5, 2020 at 5:03 pm
This is brilliant, but yeah, the only problem is that INDIRECT, especially multiple of them totally slows down a spreadsheet. I added into only one conditional format out of at least 2-dozen in a 2mb spreadsheet, and it jumped to 4mb for just one use conditional formatting formula.
Reply- Marco Koene says:October 8, 2020 at 4:16 pm
You could try this formula in your Validation Source:
=OFFSET[tables!$A$2;0;0;COUNTA[tables!A:A]-1;1]where your table is in tables!A:A
Leave a Reply
Name [required]
Mail [will not be published] [required]
Website
Δ
How to comment
How to add a formula to your commentInsert your formula here.
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form