## 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 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)

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 […]

### 32 Responses to “How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**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

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.

Ralf,

read this post:

Create dependent drop down lists containing unique distinct values

Thank you! This tip has saved me a whole lot of effort.

Chris Hills,

Thank you for commenting!

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 answer

It's possible "data validation" in collaboration mode, because is not work for me (2002, 2007)?No!

Avoiding Shared Workbooks in Excel

Thank You

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?

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.

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

Trevor,

thank you for commenting!

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.

Thank You!!!!!

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

Tony

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

Tony,

Thank you for posting a workaround.

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!

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 → […]

Thank you !

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!

good article... simply explained.

I'll come back for more tips...

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?

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?

I forget about INDIRECT. Great post.

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!!!

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

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.

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.

Thank you very much, Oscar. A real time saver.

It is valuable information, indeed.

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.

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