SOLVED

My data validation isn't working in Excel 365. I guess there's a bug.

Brass Contributor

I've made a a dropdown list based on a formula.
The formula works if I apply changes in Data validation every time I restart the file, and in some cells it works without even refreshing.
There has got to be some bug.
I'm attaching the file here.

6 Replies
The question isn't really clear. Agreed that you're able to create list data validation dropdown. The data validation is expected to always work whenever you open the Excel workbook
best response confirmed by Nishkarsh31 (Brass Contributor)
Solution

@Nishkarsh31 

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.

@Nishkarsh31 

My guess that is combination of performance and errors in validation formulas (if referenced cell in column to the left is empty formula returns an error). Mainly performance.

 

Agree with @Riny_van_Eekelen , it's better to rebuild the model.

@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.

@Nishkarsh31 

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 @Sergei Baklan already hinted as to why it doesn't work most of the time.

@Nishkarsh31 

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.

1 best response

Accepted Solutions
best response confirmed by Nishkarsh31 (Brass Contributor)
Solution

@Nishkarsh31 

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.

View solution in original post