I'm new to Excel so apologies if this is a simple task but I've been wrecking my head over it for a few days now, looking at videos and reading guides but none have really helped me with what I need to do.

I'm looking to create 3 drop down boxes, with the next drop down dependent on the previous.

So in G1 if I select Football, in G2 it should come up with a list of "La Liga, Premier League", and if I select "Premier League" then G3 should come up with "Liverpool vs Manchester United, Manchester City vs Wolverhampton Wanderers".

Any help would be greatly appreciated. Thanks.

CodePudding user response：

This can be accomplished by creating an auxiliary/preparation table, which I have created in columns I:K (but can be in any other range).

First, repeat the headings of your main table in the range I1:K1. In cell I2, enter the following formula: =UNIQUE(A2:A12); in cell J2 the following formula: =UNIQUE(FILTER(B2:B12, A2:A12=G1)); and in cell K2 the following formula: =UNIQUE(FILTER(C2:C12, (A2:A12=G1) * (B2:B12=G2))). These steps are used to create the unique list of observations that meet certain conditions, e.g., being a competition in a particular sport.

Now you need to set up your lists based on the dynamic ranges. For the first list, go to the cell G2 -> tab Data -> Data Validation -> Allow: List -> Source: =$I$2#.

Repeat this process for the cells G3 and G4, while changing the source to =$J$2# and =$K$2#, respectively.