Update a validation list based on value of another cell

I've attached sample dummy data to illustrate what I'm trying to do.

In sheet 1, there are two columns, Category and Subgroup. The cell validation for Category is pulled from the category list on sheet 2. Basically, what I want to happen is depending on what is selected as the category, only the related sub-groups would be populated in the sub-group validation dropdown list. In other words, if Fruit is selected as the category, I only want Apple, Orange, Pear, Banana, etc to be in the validation drop down list for the sub group. If vegetable, then only Corn, Green Bean, Cucumber, and Peas should be shown.

I've included two options for the relationship list as I'm not tied to either style and not sure which would be easier to work with.

I've tried going down a couple of different paths but keep getting caught at a dead end.

Thanks for your help with this in advanced. I appreciate it.

Take a look at this...I've had this in my files for dependent validation boxes....this shows an example of what you want to do....also has code for if you want the secondary validations to clear if there is a change to the primary.

Hello GuruWannab,

When posting answers you should include some explanation of the solution along with any pertinent code. There are occasions were the amount of code prohibits posting it all or providing an in depth explanation of it. That is definitely not the case here. The forum is a place to share information and learn from others. Please amend your post to include more details.

My apologies...I thought the documentation in the file was enough...but sometimes you get to a point where you can look at something and absorb how it works and you don't realize that others can't. Ohhhh I feel like Sylar on Heroes!....lol

In anycase...in the spreadsheet I attached...the dependent validations rely on named ranges...each having a respective relationship with items in other ranges.

For example, if you highlight A2:a5 - you will see that is a named range "animals". Your validation list can be set for the named range instead of a range of cells and their values. If you highlight B2:b5 - the named range is "Canine". Using named ranges...you can have one validation set to select what type of animal...then the second validation is =indirect(the first validation cell) and it will then seek out that range name and use the range as the available selectables.

In the sheet...there are two examples of dependent validations..the top one you will notice that if you change the CLASSES...the other two automatically blank out....per the description on the sheet...you can right click on the tab and select VIEW CODE...there is a vba code built that anytime there is a change to a more dominent validation cell...the subordinates clear out. This does not happen to the second set of validations on row 17 as the code is designed only to look at the row 4 validations in I and J columns

Hope that helps

Hello GuruWannaB,

Thank you for adding the write up.

You can modify the code where a specific cell reference is...and make it a column reference.

For example. In the first IF/NOT

Please Login or Register  to view this content.

you could change the ("H4") to ("H:H") - that would then make it that ANY change to ANY validation in the H Column will cause the validations in I4 and J4 to clear...

How many validations are you going to have? You would ideally, I would think have only 1 primary and 1 secondary and so on...

What would the purpose be to have 2 primary validations? Maybe I'm not sure what you mean...but to do it...select the respectively dependent validations and name them as a range. Then instead of the hardcoding to a particular cell..you have it hardcoded to a range that can then clear multiple cell validations.