I'm looking to have a unique Data Validation list for every cell in a single column. The list would be dynamic, only pulling the correct categories where values (I used an "X" in my example) are not blank.
My approach was this:
- Use the filter function to pull out values that have been "selected/marked"
- Set the data validation for that row as those values (including empty ones, since this will be changing)
My issue is that this way, while I can set the filter up for every row, I will have to set the validation up for every cell individually & with my large data set it isn't realistic.
I'm looking for a quicker way to execute this; I thought something like = I & ROW() : L & ROW() might be a good starting point, but I don't know how to get the cell value that needs to go in the ROW() to change, without dragging the formula down.
I also thought along the lines of rules I have used for Conditional Formatting like =$I2=<>"" , but I'm kind of drawing a blank at this point.
Any thoughts?
I made a sample sheet to show exactly what I'm trying to accomplish:
https://docs.google.com/spreadsheets/d/1GU6BqrkAOh0y3MJxbNU6OUrPDRk4YuLAJksL--AOSRM/edit#gid=0
