Result of Filtered Array (Excel 365) to appear as drop down list in data validation

[Mandy_]

Hi,

I have a filtered array result of text that appears in sheet 2. I want sheet 1 to display those results as a data validation list.

In the past I have used a table for the drop down list, but in that instance I had data that was manually entered, I wish to use data that will change dynamically.


Can a filtered array be added to a table? That would be my first preference - for all my formulas link to a table.
Otherwise how can I get the results of a filtered array to be available as a data validation list?

Thank you for your time,

 

[kevin9999]

Not sure how you get your filtered list (FILTER() maybe?) or how your data is laid out on either sheet, but hopefully the XL2BB below will give you some clues...

Book1
	A	B	C	D	E	F	G	H	I
1	Full list		Filtered list		Data validation		HDR1	HDR2	HDR3
2	a First		a First		a Second		data	data	data
3	a Second		a Second				data	data	data
4	b First		a Third				data	data	data
5	b Second						a First	data	data
6	b Third
7	a Third
8
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A15,LEFT(A2:A15,2)="a ","")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$C$2#
G5	List	=$C$2#

 

[Mandy_]

Thank you!

 

[kevin9999]

Happy to help Mandy, and thanks for the feedback