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.
Hi Jeff. Was our original source wrong, incomplete or did we just miss the memo?
The latter, I think. Nice to see you’re up in time for lunch, Oli. :-)
Hi, i just saw your article and i was wondering, how did you find out that data validation is not volatile?