Forum Discussion
Dynamic data validation based on another column in table
I'm trying to get the data validation (values from another table) dynamic, in a table and based on the value in another column.
I manage to get all values from my other table in data validation using =INDIRECT("TableName[ColumnName]"). But I like to filter the values by the selection in the target table.
I've asked ChatGPT, but it only leads me in circles - so now I hope for you super users in here! 😏
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.
8 Replies
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/zbsqGjNbPKgHope 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?
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.
Thanks! :) I like the approach in 1. where I can keep all category values in one and the same list/table. I'll try to find the best way to make the "help sheet" 'INDIRECT' to keep up with formulas when the target table growth.
If your version support TRIMRANGE() feature then we can make it more dynamic and automated.
This should get you going: https://www.contextures.com/xldataval02.html
