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:

  1. Use the filter function to pull out values that have been "selected/marked"
  2. 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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.