Validate data against a table

I am a big fan of Excel tables – they make my life easier in many different ways. One of the biggest ways is through the ease of referencing table content and summary data.

One task that is not so obvious is how to validate a cell against a list of contents from a table. In the Data Validation dialogue, it would make sense to be able to enter the table name and column name in the following way:

Unfortunately this doesn’t work as the table/column syntax is not recognised within the formula. There is however a very simple workaround: the INDIRECT() function.

As can be seen above, wrapping the reference to the table/column in the INDIRECT() function allows the reference to be evaluated correctly.