Clear 2nd Dependent Drop Down Excel

Question

I finally figured out how to make a dependent drop down. FINALLY.

My problem is when I change the first drop down, the second one still has the options from the first option.

Drop down 1:

Fruit
Vegetables

Fruits Drop Down info: Apples
Bananas

Vegetables Drop Down info: Carrots
Cucumbers

If I choose Fruit, I can choose Apples. However, if I choose Vegetables, Apples is still an option until I change it.

Is there a solution to purge the second drop down every time a change is made on the first one?

I found: Dependent drop down list in excel auto update but I'm confused on how to make it work for my situation. Drop down is in A10 and secondary is A20. My next plan is to learn VBA.

Answer 1

I realized I was doing this all the hard way. Instead of doing a second drop down, I removed it and I decided to do HLOOKUP. Then I could find the word in question.

Many thanks to all those that have answered.

Answer 2

In the second drop down, you can use formulas. If your fruit choices are in one column, and the vegetable choices are in another, you can do something like the following...

Assuming that your Drop Down 1 is in A10, your list of Fruits is C1:C10, list of Vegetables is D1:D10:

For the second drop down in cell A20, go to Data Validation -> List. Instead of just choosing your list of either Fruits, or Vegetables, do this =If(A10="Fruit",c1:c10,d1:d10).

Then, as you change the A1 value from Fruits to Vegetables, your second drop down will update the list choices.

Edit: I don't know if you are going to be adding to those lists of fruits/vegetables, but you can also used named ranges. If you name all of your Fruit choices as, say, "Fruit", and vegetables as "Vegetables", the data validation can use these named ranges: =If(A10="Fruit",Fruits,Vegetables).