Multiple Drop Downs

KyleC2022

New Member
Joined
Dec 5, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
MrExcelPlayground14.xlsx
ABCDEFG
1FruitRedVegFruitBlue
2VegBlueBlueVegPurple
3FruitYellow
4FruitPink
5VegPurple
6FruitBlack
Sheet20
Cell Formulas
RangeFormula
F1:F2F1=UNIQUE(A1:A6)
G1:G2G1=IFERROR(UNIQUE(FILTER(B1:B6,A1:A6=D1)),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D1List=F1#
D2List=G1#
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
By the way, you can put a SORT around the UNIQUE if you want your dropdown options in alphabetical order.
 
Upvote 0
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 AncillaryLegs
Cabinet AncillaryShelf Pegs
Cabinet AncillaryBuffers
Cabinet AncillaryBig Hangers
Cabinet AncillaryPWS Hangers
Cabinet AncillaryConcealed Hangers
DrawerRunner ANT450L
DrawerRunner ANT450R
DrawerSide ANT500L
DrawerSide 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.
ABC
1DrawerSide ANT500L£12.34
Above DropdownAbove DropdownAbove '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!
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top