Forum Discussion
My data validation isn't working in Excel 365. I guess there's a bug.
Allow me to say that it's amazing that the drop-downs worked at all (even sometimes). Downloaded your schedule and experienced the same problems. Your formulae can be easily avoided if you assign Named Ranges to your data validation lists. The Data Validation formula can then be as simple as "=indirect(cell)". I've reworked your schedule in the first 15 rows of the "Expenses" sheet to demonstrate how this works. First, I removed all your data validation and re-entered ones based on Named Ranges for the second level drop-downs. I expect that these will work all the time. Just be ware that Named Ranges only accept letters and underscores ("_"). But I'd rather use that than overly complex formulae that try to mimic the same functionality but don't work properly.
You can easily make Named Ranges dynamic. Just include a blank cell at the bottom of each list. As long as you insert new items in the lists above the last (blank) cell, the Named Range automatically expands.
Why Excel works sometimes and sometimes not, I don't know. I think SergeiBaklan already hinted as to why it doesn't work most of the time.
Riny_van_Eekelen not true in later version of Office having just tested.
joebowers Beware that you are responding to an almost 5 year old thread. A lot has happened in Excel since then. Please elaborate about what is "not true".