Validation List based on different column value

[Hamasuta]

Here's another one that I can't seem to find anywhere. I am not looking for nested validation or dependent drop downs, I know how to do that. Maybe what I want is simpler but I'm just not seeing a solution... I have attached a sample worksheet. What I want is a self-populating list that is dependent on a single criteria, but that criteria changes. If certain conditions are met, then the criteria "unlocks", and all unlocked values go in the list. Please check out the sample to see what I am asking for. I'm pretty frustrated with it right now. I know a bit of VBA, and am not shy of it, so maybe that holds the solution? Advice appreciated. https://www.dropbox.com/s/n5nu4m73ulj2km9/sample problem.xlsx?dl=0

 

[RanOtherwise]

Hi there, Hamasuta!

I looked at your example spreadsheet, and honestly, you are looking at some kind of list-validation formula, that will be like, "INDEX(LARGE(SUMPRODUCT(IF..." and such logic. I think that defeats your "simpler solution" requirement, but I cannot see a simpler way to do what your data needs.

Not sure what you mean by "nested validation" or "dependent drop downs", but it's going to look ugly one way or another....

Maybe you better define how the items are updated, and what you want your validation lists to do - because in Excel, there is always complexity regardless of manner of execution.

Best,
-Ran