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.
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.
Yes it does, I've never used it though. Please explain how it can be done. :)
I have made some small changes in your example and put the data into tables.
Now I have a warning (red) if the formulas don't cover the rows in Table1 - but it would be nice if the range of formulas could expand dynamically. 😏 (Otherwise I just add "1000 rows" to be sure.)
