VBA Data Validation Question

[ZooTV92]

I have a range of cells from Q17:Q167 where I need data validation to change around based on values in the range E17:E167. If column E contains an asterisk, then the cell on the same row in column Q should be validated with the dynamic range named "title1". Since the values in col E change (upon a button click), the same row will not always have the asterisk, therefore validation should be deleted from the cells that don't have an asterisk in Col E. I can't seem to wrap my head around how to solve this without involving conditional formatting, which would prevent the drop down from being used, but the drop down exists, and would be confusing to users wondering why there is a drop down in a cell where there shouldn't be.

I hope that makes sense. It's not possible to load an example of the spreadsheet as it is quite large and contains sensitive information, but I include a basic idea of what I'm after.

row# Col E Col Q

17 * validation
18 * validation
19 fsdfsf no validation
20 * validation

after a button click the information changes

row# Col E Col Q
17 dfsdfsd no validation
18 * validation
19 * validation
20 dfsdfsfs no validation

Thanks for looking!

[dflak]

You live and you learn. I never knew that the range validation for a list validation could take an if statement.
=IF($E2="*",Title1,FALSE)

See the attached. You'll have to change the cell references to match you setup.

[ZooTV92]

I actually got that far. The issue is that the cells without the corresponding asterisk still have the drop down menu, even though it's "deactivated". I was hoping there would be a way that for those cells there would be no validation at all, meaning no drop down menu if you were to click on the cell....That's why I was trying to steer away from conditional formatting, and stick with VBA, if it's even possible.....

[ZooTV92]

Finally figured it out.

Please Login or Register  to view this content.