dropdown list based on filter function result

[gudis22]

Hi,

I am trying to create a dropdown list based on the results from a filter function.
Here's an example :
I used Filter function (in B1) to get the results containing the word rose from column D.
However this fills the cells B1 to B3, instead I would like to get a dropdown menu. I used (=B2# in data validation) to create a dropdown menu but that did not work. Is there any other way to do it? Or is it possible to get a dependent drop down list based on the results of filter function or Vlookup function?

A	B	C	D
rose	=FILTER(D1:D7; A1=C1:C7)	rose	rose from A
lotus		rose	rose from B
apple		rose	rose from C
		lotus	lotus from a
		lotus	lotus from b
		apple	apple from a
		apple	apple from b

 

[kevin9999]

I’m not 100% clear on what you are looking for, but I’m assuming it’s one of the 2 options outlined below.

Option 1
Move your Filter() formula from B1 to E1

• Create a named range called MyList_1
• In the Refers to box for MyList_1 type =OFFSET(option_1!$E$1,0,0,COUNTA(option_1!$E:$E),1)
• “option_1!” is the sheet name – change to suit
• In B1 create a data validation dropdown with the following:
• Allow: List
• Source: =MyList_1

Filter.xlsb
	A	B	C	D	E
1	rose	rose from A	rose	rose from A	rose from A
2	lotus		rose	rose from B	rose from B
3	apple		rose	rose from C	rose from C
4			lotus	lotus from a
5			lotus	lotus from b
6			apple	apple from a
7			apple	apple from b
option_1

Cell Formulas
Range	Formula
E1:E3	E1	=FILTER(D1:D7,A1=C1:C7)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MyList_1

Option 2
Move your list (rose, lotus, apple) from A1:A3 to E1:E3

• Highlight E1:E3 and name that range MyList_2
• In A1 create a data validation dropdown with the following:
• Allow: List
• Source: =MyList_2

Filter.xlsb
	A	B	C	D	E
1	rose	rose from A	rose	rose from A	rose
2		rose from B	rose	rose from B	lotus
3		rose from C	rose	rose from C	apple
4			lotus	lotus from a
5			lotus	lotus from b
6			apple	apple from a
7			apple	apple from b
option_2

Cell Formulas
Range	Formula
B1:B3	B1	=FILTER(D1:D7, A1=C1:C7)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=MyList_2

 

[Peter_SSs]

I used Filter function (in B1) to get the results containing the word rose from column D.

I'm not sure why you are using column C, but move the filter function as suggested. I have used column F and that column could be hidden if you want.
No need to name a range though, just use the spill range in the Data Validation.

22 01 31.xlsm
	A	B	C	D	E	F
1	rose			rose from A		rose from A
2	lotus			rose from B		rose from B
3	apple			rose from C		rose from C
4				lotus from a
5				lotus from b
6				apple from a
7				apple from b
8
DV List

Cell Formulas
Range	Formula
F1:F3	F1	=FILTER(D1:D7,ISNUMBER(SEARCH(A1,D1:D7)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=F1#

 

[gudis22]

@kevin9999 and @Peter_SSs, Thank you very much for the quick reply. All options mentioned above worked really amazing. Thanks again

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.