Data Validation doesn’t care about volatility.

Huh. All these years I’ve been telling people to avoid volatile functions in models – especially in dropdowns because large chains of dependents usually hang off of these – and it turns out that I’m wrong in that specific case, as per Roberto’s comment in this thread.

If you use a volatile function to feed data validation, then the formulas downstream of that data validation cell only get recalculated when you select something new from the dropdown. That is, it behaves just like a non volatile function.

Goodbye clunky INDEX-based cascading dropdowns. Hello INDIRECT and OFFSET-driven cascading dropdowns.

3 thoughts on “Data Validation doesn’t care about volatility.

  1. Hi, i just saw your article and i was wondering, how did you find out that data validation is not volatile?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.