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]

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))

If these functions are acceptable, then it is much easier, but I'm not sure. Using functions that are usable only in latest Excel will limit portability to older version on some PC.

 

[Zot]

No worries, Thanks anyway. Have a good day

My bad. I forgot about not using macro but I suggested in Post #8.

The advise I gave was wrong. If you sort the source such as sorting the Name, the Country will will not be sorted. If you sort the Country, then the Name will be jumbled.

Therefore, unless it can be done within the formula inside validation there is no way you can have each drop down sorted. I'm not sure the new Excel 365 SORT function can do it or not but that will limit to PC with 365 only I guess.

 

[Beatrice]

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))

Thanks but in version 2019, these function of "SORT" and "UNIQUE" not validate.

 

[Beatrice]

My bad. I forgot about not using macro but I suggested in Post #8.

The advise I gave was wrong. If you sort the source such as sorting the Name, the Country will will not be sorted. If you sort the Country, then the Name will be jumbled.

Therefore, unless it can be done within the formula inside validation there is no way you can have each drop down sorted. I'm not sure the new Excel 365 SORT function can do it or not but that will limit to PC with 365 only I guess.

Thanks Zot for follow up my case, I will still try though. (Knowing team share with google sheets do not suppose VBA).