INDIRECT Function How I Loved Thee

I was reading some posts on StackOverFlow around VOLATILE functions in Excel - something I really knew nothing about. Two of my favorite functions have been OFFSET and INDIRECT. Both of these are volatile functions and can be replaced by other non-volatile functions like INDEX, CHOOSE, etc. So I will start to use the non-volatile choices first (starting soon, I promise).

However, one place where I often use the INDIRECT function is for data validation. If I have a table called tabColor with a column called Color, and I want to use that for data validation, you would think you could set up the data validation as a list with this formula:

=tabColor[Color]

However, this causes an error. If you wrap the formula with INDIRECT, it works:

=INDIRECT("tabColor[Color]")

So, I have two questions:

1. Will using INDIRECT in data validation cause the cell to be marked as volatile?


2. Is there a non-volatile way to use a table for data validation?

Btw, if you want to know more about volatile functions, a nice write-up can be found at chandoo.org