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.
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.
Riny_van_Eekelen
Hi, thanks for the response.
My initial plan was to go for Named ranges, but since I had to make the dropdown list dynamic (More items would be added), I decided to go for the formula.
Otherwise I would have to edit name ranges every time I add an item.
Anyway, I guess I'll have to go for that only.
However I'm just curious as to why excel, makes it work sometimes.
What I did
1) Renamed Dropdown sheet into DD to make formulas shorter (otherwise they are under the limit for named formula)
2) Corrected data validation formulas accordingly
3) Added them as named formula to Name manager (IncomeList, ExpenseList, etc.)
4) Changed in DataValidation initial direct formulas on named formulas (e.g. =ExpenseList)
No changes in data model and formulas itself. Now it works, at least I tried couple of times close and open file, seen no issues with data validation.
I found same issue on another old file and named formulas helped. It looks like something is slightly changes in Excel behaviour, perhaps with dynamic arrays introduction. However, same issue if open the file on semi-annual channel (pre-DA Excel), but most probably engine is already updated for these builds as well.
It'll be interesting to play with Excel 2013, so far had no such possibility.
Anyway, updated file is attached.
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".
