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.
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:
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: