Limit List Box to Certain Rows in a Data Table

[ormy28]

Hi

I am currently working on a very large data set contained within a table that includes two columns, the name of a football club in column A and the level of the club (e.g. first team, reserves, U-23, etc) in column B.

There are hundreds of different 'Clubs', and 10 different 'Levels'. Each Club has differing number of Levels associated with it, some have just 1 and some have all 10.

The user will use a list box to select the name of a football club in D2, and then in E2 I would like them to be able to select a Level, also from a list box. However, I want to limit that list box to only the levels associated with that club.

For example in the below if they were to select 'Preston' in D2, then the drop down list in E2 should only include the options 'First Team' and 'Junior'. If they were to select 'Bolton' in D2, then the drop down list in E2 should include 'First Team', 'U-23', 'Reserves' and 'Junior'.

I know you can create dependent drop down lists by creating named ranges in a separate sheet for each Club, listing all the Levels associated with that Club, then use Indirect in the data validation. This is the method I have used to create the drop down list in D2. However, it isn't really an option here as the data set is huge, and it would take a lot of maintenance as the data set changes over time.

Is there a way to force a drop down list in E2 to contain only the Levels appropriate for the Club selected in D2?

Hopefully the example below make things a bit clearer. One point to note, the data table in columns A and B would be in a different sheet than the Club and Level selections in columns D and E.

Book1
	A	B	C	D	E
1	Club	Level		Selection	Select Level
2	Morecambe	First Team		Bolton
3	Bolton	Junior
4	Carlsile	First Team
5	Preston	First Team
6	Morecambe	U-23
7	Bolton	U-23
8	Carlsile	U-23
9	Carlsile	Reserves
10	Preston	Junior
11	Bolton	Reserves
12	Newcastle	First Team
13	Newcastle	Reserves
14	Sunderland	First Team
15	Bolton	First Team
16	Newcastle	U-23
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=INDIRECT("Clubs")

 

[Fluff]

Do you just want one data validation cell each (ie D2 & E2) or do you want multiple rows?

 

[ormy28]

Do you just want one data validation cell each (ie D2 & E2) or do you want multiple rows?

Hi. There only needs to be data validation in D2 and E2. The dropdown list in D2 works fine, it's just having the dropdown list in E2 limited to specific Levels dependent on what is selected in D2.

 

[Fluff]

Ok, with a couple of helpers

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Club	Level					Selection	Select Level
2	Morecambe	First Team		Morecambe	First Team		Preston	Junior
3	Bolton	Junior		Bolton	Junior
4	Carlsile	First Team		Carlsile
5	Preston	First Team		Preston
6	Morecambe	U-23		Newcastle
7	Bolton	U-23		Sunderland
8	Carlsile	U-23
9	Carlsile	Reserves
10	Preston	Junior
11	Bolton	Reserves
12	Newcastle	First Team
13	Newcastle	Reserves
14	Sunderland	First Team
15	Bolton	First Team
16	Newcastle	U-23
17
Master

Cell Formulas
Range	Formula
D2:D7	D2	=UNIQUE(A2:A16)
E2:E3	E2	=UNIQUE(FILTER(B2:B16,A2:A16=G2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2:H2	List	=D2#

 

[ormy28]

Excellent, thanks for that. Works a treat.

And thanks for introducing me to the UNIQUE function. That should simplify quite a few things for me in future.

 

[Fluff]

You're welcome & thanks for the feedback.