Jan 18 2020 07:54 AM
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.
Jan 18 2020 09:10 AM
Jan 18 2020 01:17 PM - edited Jan 18 2020 01:18 PM
SolutionAllow 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.
Jan 18 2020 04:00 PM
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.
Jan 19 2020 06:14 AM
@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.
Jan 19 2020 06:38 AM
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.
Jan 23 2020 10:26 AM
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.
Jan 18 2020 01:17 PM - edited Jan 18 2020 01:18 PM
SolutionAllow 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.