How to make a list conditional upon another list?

[RedMisterExcel]

Hey guys, potentially dumb question, but I want to create a drop-down list that can spawn a selection of further drop-down lists from another cell contingent on the entry in the first list.

For example: List A = Fruit, Vegetables, Drinks

List B can be one of these three depending on which of the entries in List A was selected first: Apples, Oranges, Bananas; Celery, Broccoli, Carrots; or Pepsi, Coke, Sprite

Is that possible to do?

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E
1	Item	Type	Type	Item	Fruit
2	Apple	Fruit	Fruit	Apple	Orange
3	Orange	Fruit	Vegetable	Orange
4	Banana	Fruit	Drink	Banana
5	Celery	Vegetable
6	Croccoli	Vegetable
7	Carrots	Vegetable
8	Pepsi	Drink
9	Coke	Drink
10	7up	Drink
Sheet20

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(B2:B10)
D2:D4	D2	=FILTER(A2:A10,B2:B10=E1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=C2#
E2	List	=D2#

 

[RedMisterExcel]

MrExcelPlayground13.xlsx
	A	B	C	D	E
1	Item	Type	Type	Item	Fruit
2	Apple	Fruit	Fruit	Apple	Orange
3	Orange	Fruit	Vegetable	Orange
4	Banana	Fruit	Drink	Banana
5	Celery	Vegetable
6	Croccoli	Vegetable
7	Carrots	Vegetable
8	Pepsi	Drink
9	Coke	Drink
10	7up	Drink
Sheet20

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(B2:B10)
D2:D4	D2	=FILTER(A2:A10,B2:B10=E1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=C2#
E2	List	=D2#

Thanks man, but do you mind explaining to me how it works? I want to learn to catch a fish so I don't have to keep begging everyday if you know what I mean

 

[JamesCanale]

Sure...
Columns A and B are your data. C2 is an array formula that takes the unique values in B - and 'spills' into C3 and C4. If there were more than 3 unique values in B, it would spill further.

In E1, there is Data Validation rule - picking from a List in that spilling array - C2# means to take whatever is the whole unique list from B. That drop down changes the same as C2.

In D2, it filters column A by what is selected in E1. So when "Fruit" is selected in E1, D2 filters all of A for anything with "Fruit" in B.

Then the E2 data validation rule - picks from the list of D2# - another spilling array.

 

[RedMisterExcel]

Sure...
Columns A and B are your data. C2 is an array formula that takes the unique values in B - and 'spills' into C3 and C4. If there were more than 3 unique values in B, it would spill further.

In E1, there is Data Validation rule - picking from a List in that spilling array - C2# means to take whatever is the whole unique list from B. That drop down changes the same as C2.

In D2, it filters column A by what is selected in E1. So when "Fruit" is selected in E1, D2 filters all of A for anything with "Fruit" in B.

Then the E2 data validation rule - picks from the list of D2# - another spilling array.

What does putting a # behind a cell do?

 

[RedMisterExcel]

Also, it's not giving FILTER as a valid function.

 

[Fluff]

Are you sure you have 365 rather than 2019 or an earlier version?

 

[RedMisterExcel]

Are you sure you have 365 rather than 2019 or an earlier version?

I have 2016 haha

 

[Fluff]

In that case I suggest that you update your profile.

 

[RedMisterExcel]

In that case I suggest that you update your profile.

Can't, it's a work laptop and I'm not allowed to modify its applications unfortunately.