Multiple Drop Downs

[KyleC2022]

Hi. I am struggling to find a way to create multiple drop downs that use just two columns. Using the data below for example, I need the first drop down to select the type of food. The second drop down to filter the colour. Without having to rearrange all the data. (I have 1000s of rows of data so do not want to start reorganising it.) Thanks in advance!

Column A - Column B
Fruit - Red
Vegetable - Blue
Fruit - Yellow
Fruit - Pink
Vegetable - Purple
Fruit - Black

So from data above. The first drop down I can select either Fruit or Vegetable. Example is to select Fruit. The second drop down should only give me those colours relating to fruit.

 

[JamesCanale]

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G
1	Fruit	Red		Veg		Fruit	Blue
2	Veg	Blue		Blue		Veg	Purple
3	Fruit	Yellow
4	Fruit	Pink
5	Veg	Purple
6	Fruit	Black
Sheet20

Cell Formulas
Range	Formula
F1:F2	F1	=UNIQUE(A1:A6)
G1:G2	G1	=IFERROR(UNIQUE(FILTER(B1:B6,A1:A6=D1)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=F1#
D2	List	=G1#

 

[KyleC2022]

James, Thank you so much. I copied what you did exactly and it worked. But I have an error appear in cell G1 saying "#SPILL!" I don't know what this means?

 

[JamesCanale]

Basically, the one formula in G1 will 'spill' an output. Maybe many rows (or columns or both). But if something is in the way, you get the #spill error.

So if the formula in G1 finds 10 different colors, but in G6 there is something in the cell, in G1 there will be a spill error. If there was 5 or less different colors, you wouldn't get the error and the five (or less) colors would appear in G1 to G5.

 

[Automatrix]

By the way, you can put a SORT around the UNIQUE if you want your dropdown options in alphabetical order.

 

[KyleC2022]

Hi James, really appreciate your help and this has worked so far. But doesnt quite do What I need, my fault for not explaining entirely. I have a long list of data on one tab. On a different tab I need the two drop downs to select each item I need. Once selected, I have already do an IF function that pulls a price for the item elsewhere. Currently, this gives me one drop down but then gives a full list of all items in that field. I need a second to select just one, and then gives me a price. See below short amount of data:

Cabinet Ancillary	Legs
Cabinet Ancillary	Shelf Pegs
Cabinet Ancillary	Buffers
Cabinet Ancillary	Big Hangers
Cabinet Ancillary	PWS Hangers
Cabinet Ancillary	Concealed Hangers

Drawer	Runner ANT450L
Drawer	Runner ANT450R
Drawer	Side ANT500L
Drawer	Side ANT500R

So with above sample. In a separete tab I'd like to have two drop downs. The first will allow me to select either 'Drawer' or 'Cabinet Ancillary'. The second dropdown will be a list of the items with that reference. After that, I have an IF function that will draw the price from elsewhere.

e.g.

	A	B	C
1	Drawer	Side ANT500L	£12.34
	Above Dropdown	Above Dropdown	Above 'IF' Function that pulls price from elsewhere

I will then copy this all the way down the rows so we can build up multiple products to then get a total price.

Hopefully this makes sense. Thanks in advance!