David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas:


You can also use this technique in data validation lists. I used it in the three drop down lists below. The formulas are in C13:C15.

Reference table headers in a drop down list, cell B13:


Reference a table column in a drop down list, cell B14:

=INDIRECT("Table1[First Name]")

Reference a table row in a drop down list, cell B15:


Table names in conditional formatting formulas

I used the same technique in the following example:

Rows (A13:C22) equal to a row in the table are highlighted.

Conditional formatting formula, cell range A13:C22:

=COUNTIFS(INDIRECT("Table2[First Name]"),$A13,INDIRECT("Table2[Last Name]"),$B13,INDIRECT("Table2[Company Name]"),$C13)

Download excel *.xlsx file

Reference a table in a data validation list or conditional formatting formula.xlsx