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:

image

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.

image

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

Leave a Reply

Your email address will not be published.