Excel show drop-down list based on the value of the cell

Question

Here, I have a list of red coloured fruits, and a list below it, of yellow coloured fruits, as you can see.

Here, I have two drop-down lists used to select colours and fruits. When selecting "Red", in the drop-down list on the left, I would like the list on the right to show the list of red coloured fruits only. When selecting "Yellow", in the drop-down list on the left, I would like the list on the right to show the list of yellow coloured fruits only. What kind of formula do I use to do that?

Answer 1

I would create a formula like this. The red fruit is listed in column AA. The yellow fruit is listed in column AB.
=IF(A1="Red",OFFSET(AA2,0,0,COUNTA(AA2:AA10),1),IF(A1="Yellow",OFFSET(AB2,0,0,COUNTA(AB2:AB10),1)))

In the data validation list for the cell to select the fruit input that formula above.

How it works

The IF statement evaluates the color of the fruit selected. The OFFSET command will create a list starting at the top of the selected color fruit list. The COUNTA formula counts the number of fruit in the column (up to 10 fruit).