Dynamic Data Validation List

[thedeadzeds]

Hi Guys,

Sheet 1 Column A contains a data validation list of name based on the data in Sheet 2 Column A2:A;5000.

I want to the data validation list to only show the names if column B (location) contains 'Bristol'. Is this possible?

List of Names in Sheet 2

Name List	Location
Dave	Bristol
Jim	Bristol
Steve	Bristol
Rob	Bristol
Andrew	Cardiff
Corrine	Cardiff
Sarah	Cardiff
Sally	Cardiff
Ann	Cardiff
Donna	Cardiff

 

[Peter_SSs]

Like this?

thedeadzeds.xlsm
	A
1	Dave
2	Jim
3	Steve
4	Rob
5
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=FILTER(Sheet2!A2:A100,Sheet2!B2:B100="Bristol","")
Dynamic array formulas.

 

[Peter_SSs]

.. or do you mean this?

thedeadzeds.xlsm
	A	B	C	D
1	Name List	Location		Bristol
2	Dave	Bristol		Dave
3	Jim	Bristol		Jim
4	Steve	Bristol		Steve
5	Rob	Bristol		Rob
6	Andrew	Cardiff
7	Corrine	Cardiff
8	Sarah	Cardiff
9	Sally	Cardiff
10	Ann	Cardiff
11	Donna	Cardiff
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A100,B2:B100=D1,"")
Dynamic array formulas.

thedeadzeds.xlsm
	A
1	Jim
2
3
4
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=Sheet2!$D$2#

 

[thedeadzeds]

.. or do you mean this?

thedeadzeds.xlsm
	A	B	C	D
1	Name List	Location		Bristol
2	Dave	Bristol		Dave
3	Jim	Bristol		Jim
4	Steve	Bristol		Steve
5	Rob	Bristol		Rob
6	Andrew	Cardiff
7	Corrine	Cardiff
8	Sarah	Cardiff
9	Sally	Cardiff
10	Ann	Cardiff
11	Donna	Cardiff
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A100,B2:B100=D1,"")
Dynamic array formulas.

thedeadzeds.xlsm
	A
1	Jim
2
3
4
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=Sheet2!$D$2#

View attachment 115735

This is perfect thanks so much

 

[thedeadzeds]

.. or do you mean this?

thedeadzeds.xlsm
	A	B	C	D
1	Name List	Location		Bristol
2	Dave	Bristol		Dave
3	Jim	Bristol		Jim
4	Steve	Bristol		Steve
5	Rob	Bristol		Rob
6	Andrew	Cardiff
7	Corrine	Cardiff
8	Sarah	Cardiff
9	Sally	Cardiff
10	Ann	Cardiff
11	Donna	Cardiff
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A100,B2:B100=D1,"")
Dynamic array formulas.

thedeadzeds.xlsm
	A
1	Jim
2
3
4
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=Sheet2!$D$2#

View attachment 115735

Sorry quick question with this. The filter list will grow over time as more people get added to 'Bristol'. Is there a way to dynamically grow the validation list? So today it may be a1:a5 but tomorrow this could be a1:a100? Many thanks

 

[Peter_SSs]

Not sure what you mean. The formula in D2 of Sheet2 already looks in rows 2:100 of that sheet to make the data validation list.