I'm using quite often the data validation option in Excel with the help of data listings. Usually I have my listings in a hidden sheet defined and use them on the other sheets where ever needed.
All works fine as long the defined lists are fixed, (not changing).
But in real live I have quite often dynamic lists, they are changing in the amount. In the beginning the list might have 5 or 10 entries but later it grows and need to have 15 or 20 entries.
Currently I solve this manually by opening the hidden sheet, extending the range, closing it again.
Another way is that I define right away a bigger range, let's say from 1 to 10 although I have in the beginning only values from 1 to 5. But in that case I have empty rows in the pull down list and this is neither very nice.
I'm looking for a clever solution to handle the dynamics here. E.g. I could define right away a huge range for the "maybe" values on the hidden sheet if there is a possibility to have the empty space not listed in the pulldown listing or if it would be possible to have the listing starting on top when opening and not on the buttom. See attached file.
Maybe there is a complete other and much better solution out?
It is very helpful and user friendly to have the pull down listing validation but not very user friendly to maintain them. Hope there is a nice solution out.
Thank you in advance for your help and suggestions