dependent multiple drop-down menu [formula only, no VBA please]

[Beatrice]

Hi gurus,

I wanted to create a dependent multiple drop-down menu, but since the company is using different versions of excel, also in Mac & PC. so I need it in formula only, no VBA please.
Is it possible to do so? Thanks to advise. ?‍

Type	Name	Country	Price		Type	Name	Country	Price
Meat	Pork	China	32.00
Meat	Chicken	Brazil	28.00
Meat	Chicken	China	24.00
Meat	Beef	China	38.00
Meat	Beef	Australia	42.80
Meat	Beef	Japan	68.00
Fruit	Orange	China	2.99
Fruit	Orange	Australia	4.99
Fruit	Orange	USA	3.99
Fruit	Apple	China	3.99
Fruit	Apple	Japan	6.99
Fruit	Apple	USA	5.99
Fruit	Banana	Philippines	6.99

 

[Zot]

I believe this is what you need

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[Beatrice]

Hi, I have tried the above method but it seems not function as what I wished for.
May I rephrase again, knowing all header " Type" , " Name" , "Country" and " Price" will have different information update, and ultimately I wish to have the 2nd table with drop down menu according to all update, without duplication, sorted by alphabetical order, and ignore blank. Here is what I imagined for:

Is it possible to do so? Many thanks for your advice ?‍ ?‍ ?‍

 

[Zot]

Hi, I have tried the above method but it seems not function as what I wished for.
May I rephrase again, knowing all header " Type" , " Name" , "Country" and " Price" will have different information update, and ultimately I wish to have the 2nd table with drop down menu according to all update, without duplication, sorted by alphabetical order, and ignore blank. Here is what I imagined for: View attachment 37369
Is it possible to do so? Many thanks for your advice ?‍ ?‍ ?‍

I'm sure this is possible but I am hoping someone who can beat me for solution ?

 

[Zot]

Here it is. Maybe others can do better. You can put that orange intermediate table in other sheet to hide and look cleaner.

Validation with Unique.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Type	Name	Country	Price		Type	Name	Country	Price
2	Meat	Pork	China	32		Fruit	Apple	China	3.99
3	Meat	Chicken	Brazil	28
4	Meat	Chicken	China	24
5	Meat	Beef	China	38		Meat	Orange	China
6	Meat	Beef	Australia	42.8		Fruit	Apple	Japan
7	Meat	Beef	Japan	68			Banana	USA
8	Fruit	Orange	China	2.99
9	Fruit	Orange	Australia	4.99
10	Fruit	Orange	USA	3.99
11	Fruit	Apple	China	3.99
12	Fruit	Apple	Japan	6.99
13	Fruit	Apple	USA	5.99
14	Fruit	Banana	Philippines	6.99
15
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT((Table1[Type]=F2)*(Table1[Name]=G2)*(Table1[Country]=H2),Table1[Price])
F5:F14	F5	=IFERROR(INDEX(Table1[Type],MATCH(0,COUNTIF($F$4:F4,Table1[Type]),0)),"")
G5:G14	G5	=IFERROR(INDEX(Table1[Name],MATCH(0,COUNTIF($G$4:G4,Table1[Name])+(Table1[Type]<>$F$2),0)),"")
H5:H14	H5	=IFERROR(INDEX(Table1[Country],MATCH(0,COUNTIF($H$4:H4,Table1[Country])+(Table1[Type]<>$F$2)+(Table1[Name]<>$G$2),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=OFFSET($F$5,,,COUNTIF($F$5:$F$14,"?*"))
G2	List	=OFFSET($G$5,,,COUNTIF($G$5:$G$14,"?*"))
H2	List	=OFFSET($H$5,,,COUNTIF($H$5:$H$14,"?*"))

 

[Beatrice]

Here it is. Maybe others can do better. You can put that orange intermediate table in other sheet to hide and look cleaner.

Thanks Zot! You are a life safer!

I tried to adapt to the database but there is duplicated Name and not yet sort in alphabetical order, do you have any clue to fix it?
Many thanks.

 

[Zot]

Thanks Zot! You are a life safer!

View attachment 37511

I tried to adapt to the database but there is duplicated Name and not yet sort in alphabetical order, do you have any clue to fix it?
Many thanks.

You mentioned about sorting but since it is something I haven't done for more that two validations, I really have to scratch my head and dig here and there. So, forgotten about sorting ?

There are probably more simplified method with later Excel but since you mentioned there are mixed versions of Excel, my old 2016 version I think still cover down to 2007 I hope. I will try to sort later but hope someone else beat me.

I'm about to get ready to back home now

 

[Zot]

What you can do is to sort data list. This can be done automatically each time you add data to the list. You can use Worksheet event macro

How to Sort Drop Down Lists Automatically in Excel - Excel Campus

Explore four different ways to have your data validation list automaticallly sort, inclusing macros, List Search Add-In, Power Query, and Dynamic Array Functions

www.excelcampus.com

 

[Beatrice]

You mentioned about sorting but since it is something I haven't done for more that two validations, I really have to scratch my head and dig here and there. So, forgotten about sorting ?

There are probably more simplified method with later Excel but since you mentioned there are mixed versions of Excel, my old 2016 version I think still cover down to 2007 I hope. I will try to sort later but hope someone else beat me.

I'm about to get ready to back home now

No worries, Thanks anyway. Have a good day

 

[ykinp]

not sure if it helps but I did something similar last week and just added "=SORT(formula you already have,1,1,false) to sort it alphabetically. the 1,1,false part is ascending/descending

I also used slightly different formula to the above - it might not help you if you already have a solution but maybe it will help others?

=UNIQUE(FILTER(range where answer is, range to look in=result you're looking for))
e.g. =UNIQUE(FILTER(A1:A100, B1:B100=C1))