Forum Discussion
Dynamic data validation based on another column in table
There are two way you can do it. Try the following ways.
- One way could be using some dynamic spill formula with hash # operator in data validation. See the attached file.
- Another way could be using name manager. But in this case you have to create individual name for each category. See the attached file.
Hi,
This functionality is called: Dependent drop lists. What you are looking for is a single level of dependency (simple) while you can create so many levels (I created up to 6 levels of dependencies using Dynamic arrays)
I presented about Dependent drop lists (your issue) at this event:
https://youtu.be/xgpQKd1Pukw
and Created much more powerful dependent drop lists in this YouTube Tutorial:
https://youtu.be/zbsqGjNbPKg
Hope that helps
Thanks for very nice tutorials! 🙂
But I miss one thing, and that is to make the dependent data validation works for not only one cell but for a hole table.
There I found the suggestion from Harun24HR useful, in my example I have made some changes to match my case a little.Overview:
To accept the fact that end users might use "insert row" in the target table I put a fixed reference together with an OFFSET in the data validation – this assure that the reference will not break. This approach made the target table also movable.
But this effected the number of subcategories shown in the data validation dropdown – it's seeming to be determined according to the first row (the fixed ref.)!? To solve this, I added an extra first row with the category with the largest number of subcategories (an effect of this is that categories with less subcategories got one extra row in the dropdown).
To make this setup totally dynamically I'd like to get rid of the two (one if the categories is handles in a separate table as a first step) manual fill of formulas – is it even possible to make those expand according to data growth?
Please give me feedback if something can be made in a smarter way (I'm sure there are)!🤓
If Microsoft would allow this (below) directly in data validation this wouldn’t be a problem at all, but from my understanding it's not! 🤔Can you please add a sample file here?
