If you are in this page, you are probably aware of the advantages of Excel Tables and using them. Also, you probably wonder why you cannot use its special references, also known as structural references within data validation list. Although Excel is still not supporting this as we are writing this article, there are workarounds you can use. In this guide, we’re going to show you How to use table reference within a data validation list in Excel.
Using named ranges
Although you can't use table reference within a data validation list directly, you can use a named range that refers to a table reference.
All you need to do is to select the column that holds your list items and give a name to it. The simplest way is to type a "valid" name into the reference box and press Enter after selecting the column.
Once the named range is created, you can use it within data validation list. Select the cell you want to add the data validation and open Data Validation dialog.
Type your new named range following an equal sign.
The good thing is a named range can keep a table reference as it is. Thus, you will not use the advantages of the table.
INDIRECT function for using table reference within a data validation list
Another way is to use the INDIRECT function which can return the reference given as a text string. This means that that you can give the table reference to the INDIRECT function as a string.
Although this method seems more "direct" and supports table's auto-size feature, it is vulnerable to the name changes. Because you need to give the string as a "static" value, a change on the table or column name will break the link between the table and the data validated cell.
