Multiple Dependent Drop Down Lists

T

theom893

New Member
Joined
Sep 8, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hey everyone. I am new to the forum and I have searched for what I am trying to do, but I have not been able to find anything. (could be because I am searching for something different, but would work for what I am attempting to do)

Anyway, I am trying to create three separate drop downs from a table that I have created. Each one of them dependent on the list prior. First list is choosing Material Type, second is Material Shape and the last one is Material Size. Each one of the drop downs is dependent on the previous drop down selection.

I know enough about Excel to be dangerous but not an expert by any means. I am trying to reference select cells on different sheet and I get an error message. The cells I am trying to reference have blanks cells vertically in between them and that could be the issue, but I am unsure. In cell Pricing!$E$2 I would like to have the Source Cells as..
Master!$B$3, Master!$B$15,Master!$B$18,Master!$B$23,Master!$B$32,Master!$B$41,Master!$B$45,Master!$B$48

When I do this I get an error message. I assume it is because it is not a "range". I have tried to select a range containing those cells, but then the drop down includes all of the cells, most of which are blank. I check the box to ignore blank cells, but they still show up in the list.

Any ideas for what I am doing wrong?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Welcome to the MrExcel forum!

It would have helped if you'd provided a sample of your table to work with. But based on your description, it sounds something like this:

Book1
ABCDEFGHIJK
1TypeShapeSizeFormula 1Formula 2Formula 3TypeShapeSize
2Woodsquare1Woodsquare1Glassround2
3Woodsquare2Metalround2
4Woodround1Plastictoric3
5Glass
6Metalhexagonal1
7Metaloctagonal1
8
9Plasticsquare1
10Plasticsquare2
11
12Glasssquare1
13Glasssquare2
14Glasssquare3
15Glassround1
16Glassround2
17Glassround3
18Glasstoric4
19
20
Sheet3
Cell Formulas
RangeFormula
E2:E5E2=UNIQUE(FILTER(A2:A20,A2:A20<>""))
F2:F4F2=UNIQUE(FILTER(B2:B20,A2:A20=I2))
G2:G4G2=UNIQUE(FILTER(C2:C20,(A2:A20=I2)*(B2:B20=J2)))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I2:K2List=E2#


Your table is in columns A:C. Then the E/F/G columns have formulas creating the dependent lists, and the I2/J2/K2 Data Validation rules just refer back to the formulas.

Hope this helps!
 
Upvote 0
My apologies, I tried to add a Mini Sheet, but I could not. I figured it out this morning as I did not unblock the add-in. Anyway, my table is setup very differently from what you have given as an example. I have also added the first sheet which is where I want to setup the drop downs.

I would like "Pricing" E2 to choose from "Master" B2, B15, B18, B23, B32 and B41.
Then dependent on which material is chosen, I would like "Pricing" F2 to choose from "Master" Columns D, G, J, M, P, S and V.
Then dependent again on the material and shape chosen, I would like "Pricing" G2 to choose from the different sizes in "Master" Columns D, G, J, M, P, S and V according to the the first two tables.

Hopefully this makes a little more sense.

Moline PO - SX 7100 New Revision.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1
2AnglePrice/InchBQSPrice/InchFlatPrice/InchHexPrice/InchRoundPrice/InchSquarePrice/InchTubePrice/Inch
3Alum. - 60612" x 3" x 3/8"$1.38901/4" x 2"$0.69201"$0.48501-1/2"$1.8500
41/4" x 5"$1.3156
53/8" x 2"$0.6500
61/2" x 2"$0.8220
71/2" x 6"$3.5100
81" x 2"$1.5686
91" x 3-1/2"$2.1900
101-1/2" x 2"$2.0800
111-1/2" x 4"$4.1700
122" x 2-1/2"$3.2000
13
14AngleBQSFlatHexRoundSquareTube
15Brass3/4" x 1"$2.7507
16
17AngleBQSRoundSquareTube
18Bronze5/8"$2.4560
191"$5.0277
201-1/4"$7.0223
21
22AngleBQSFlatHexRoundSquareTube
23Carbon - 10183/8" x 3/4"$0.49701/2"$0.27103/4"$0.5024
243/8" x 1"$0.56003/4"$0.3815
251-1/4"$0.7850
261-3/8"$0.9071
272"$1.8405
282-1/4"$2.2400
293"$3.9958
30
31AngleBQSFlatHexRoundSquareTube
32Carbon - 11441"$0.2710
331-1/8"$0.3815
341-1/4"$0.7850
351-1/2"$0.9071
361-5/8"$1.8405
372"$2.2400
382-1/2"$3.9958
39
40AngleBQSFlatHexRoundSquareTube
41Carbon - 12153/4"$0.4900
422"$1.9400
43
Master


Moline PO - SX 7100 New Revision.xlsx
ABCDEFGHIJKLMNO
1Job #LineProductQty OrdMaterialShapeSizeInchesStockOrderMat'l $/"Mat'l EaLine LaborLabor EaPrice Ea
2$1.00 $20.00
3$2.00 $19.00
4$3.00 $18.00
5$4.00 $17.00
6$5.00 $16.00
7$6.00 $15.00
8$7.00 $14.00
9$8.00 $13.00
10$9.00 $12.00
11$10.00 $11.00
12$11.00 $10.00
13$12.00 $9.00
14$13.00 $8.00
15$14.00 $7.00
16$15.00 $6.00
17$16.00 $5.00
18$17.00 $4.00
19$18.00 $3.00
20$19.00 $2.00
21$20.00 $1.00
Pricing
Cell Formulas
RangeFormula
N2:N21N2=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O21O2=IF(ISBLANK(D2)," ",L2+N2)
L2:L21L2=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
Cells with Data Validation
CellAllowCriteria
E2:E21List=Master!#REF!
 
Upvote 0
Thank you for using the mini-sheets, it helps a great deal with investigating your request. The downside is that your table is not very well suited to creating drop-down lists. However, let's see what I can come up with.

We still need a place to put the formulas for the drop-downs. For now, I put them on the Master sheet. This might not be a great place if you expand the table, but they're easy enough to move further to the right, or put them on a work sheet which you can hide. Here are the formulas I came up with:

Book1
AAABAC
1Formula 1Formula 2Formula 3
2Alum. - 6061Flat1/2"
3BrassRound3/4"
4BronzeSquare1-1/4"
5Carbon - 10181-3/8"
6Carbon - 11442"
7Carbon - 12152-1/4"
83"
9
10
Master
Cell Formulas
RangeFormula
AA2:AA7AA2=LET(table,B2:W100,a,INDEX(table,0,1),FILTER(a,a<>""))
AB2:AB4AB2=LET(table,B2:W100,f,INDEX(table,0,1),m,MATCH(Pricing!E2,f,0),TRANSPOSE(FILTER(INDEX(table,m-1,0),(INDEX(table,m,0)<>"")*(MOD(COLUMN(table)-COLUMN(f),3)=2))))
AC2:AC8AC2=LET(table,B2:W100,f,INDEX(table,0,1),m,MATCH(Pricing!E2,f,0),c,MATCH(Pricing!F2,INDEX(table,m-1,0),0),r,INDEX(table,m,c):INDEX(table,ROWS(table),c),b,MIN(IF(r="",ROW(r)-ROW(INDEX(table,1,1))+1,""))-1,INDEX(table,m,c):INDEX(table,b,c))
Dynamic array formulas.


They are fairly complicated. I tried to make it easy to change the table size and/or location by putting the table range at the start of the formula. As long as your table stays the same format, that's all you'll need to change. If you move the formulas to another sheet, you'll need to add the sheet name to the range, e.g. Master!B2:W100.

Now the drop-down formulas just point to these formulas:

Book1
ABCDEFGHIJKLMNO
1Job #LineProductQty OrdMaterialShapeSizeInchesStockOrderMat'l $/"Mat'l EaLine LaborLabor EaPrice Ea
2Carbon - 1018Round1-3/8"1 20
Pricing
Cell Formulas
RangeFormula
L2L2=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
N2N2=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2O2=IF(ISBLANK(D2)," ",L2+N2)
Cells with Data Validation
CellAllowCriteria
E2:G2List=Master!AA2#


One thing about the drop-downs, if you change one of them, the value of the others won't change, although the drop-down list will adapt. You might want to add an event that will clear the drop-downs to the right if one of them changes value. Let me know if that's of interest, and let me know if this works for you.
 
Upvote 0
Solution
THANK YOU so much Eric!! That has worked out perfectly!!! One last question... Is there a way to take the price from the Master Sheet and input it into Pricing Sheet/Column K based off of the selections from the three drop downs?

Ex... Alum - 6061, Flat, 1" x 2".... Price from MASTER!K8 inputted into PRICING!K2

I did put the tables into a separate sheet just to keep the people using it, from changing anything. I will protect the sheet as well.

I have changed a couple of other things as well, so I am attaching all three of the sheets to this response.

Moline PO - SX 7100 New Revision.xlsx
ABCDEFGHIJKLMNO
1Job #LineProductQty OrdMaterialShapeSizeTotal Inches for jobIn StockNeed to OrderMat'l $/"Mat'l EaLabor Hrs.Labor EaTotal Price Ea
212341Plate10Alum. - 6061Flat1" x 2"30020.00$132.00$132.00
3Stainless - 304Flat1/4" x 1"
4Stainless - 304Flat1/4" x 1"
5Stainless - 304Flat1/4" x 1"
6Stainless - 304Flat1/4" x 1"
7Stainless - 304Flat1/4" x 1"
8Stainless - 304Flat1/4" x 1"
9Stainless - 304Flat1/4" x 1"
10Stainless - 304Flat1/4" x 1"
11Stainless - 304Flat1/4" x 1"
12Stainless - 304Flat1/4" x 1"
13Stainless - 304Flat1/4" x 1"
14Stainless - 304Flat1/4" x 1"
15Stainless - 304Flat1/4" x 1"
16Stainless - 304Flat1/4" x 1"
17Stainless - 304Flat1/4" x 1"
18Stainless - 304Flat1/4" x 1"
19Stainless - 304Flat1/4" x 1"
20Stainless - 304Flat1/4" x 1"
21Stainless - 304Flat1/4" x 1"
22Stainless - 304Flat1/4" x 1"
23Stainless - 304Flat1/4" x 1"
24Stainless - 304Flat1/4" x 1"
25Stainless - 304Flat1/4" x 1"
26Stainless - 304Flat1/4" x 1"
Pricing
Cell Formulas
RangeFormula
N2:N26N2=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O26O2=IF(ISBLANK(D2)," ",L2+N2)
L2:L26L2=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
Cells with Data Validation
CellAllowCriteria
E2:E26List=Tables!$B$2:$B$11
F2:F26List=Tables!$C$2:$C$8
G2:G26List=Tables!$D$2:$D$62



Moline PO - SX 7100 New Revision.xlsx
BCDEFGHIJKLMNOPQRSTUVW
1
2AngleBQSFlatHexRoundSquareTube
3Alum. - 60612" x 3" x 3/8"$1.38901/4" x 2"$0.69201"$0.48501-1/2"$1.8500
41/4" x 5"$1.3156
53/8" x 2"$0.6500
61/2" x 2"$0.8220
71/2" x 6"$3.5100
81" x 2"$1.5686
91" x 3-1/2"$2.1900
101-1/2" x 2"$2.0800
111-1/2" x 4"$4.1700
122" x 2-1/2"$3.2000
13
14AngleBQSFlatHexRoundSquareTube
15Brass3/4" x 1"$2.7507
16
17AngleBQSRoundSquareTube
18Bronze5/8"$2.4560
191"$5.0277
201-1/4"$7.0223
21
22AngleBQSFlatHexRoundSquareTube
23Carbon - 10183/8" x 3/4"$0.49701/2"$0.27103/4"$0.5024
243/8" x 1"$0.56003/4"$0.3815
251-1/4"$0.7850
261-3/8"$0.9071
272"$1.8405
282-1/4"$2.2400
293"$3.9958
30
31AngleBQSFlatHexRoundSquareTube
32Carbon - 11441"$0.2710
331-1/8"$0.3815
341-1/4"$0.7850
351-1/2"$0.9071
361-5/8"$1.8405
372"$2.2400
382-1/2"$3.9958
39
40AngleBQSFlatHexRoundSquareTube
41Carbon - 12153/4"$0.4900
422"$1.9400
43
44AngleBQSFlatHexRoundSquareTube
45Plastic
46
47AngleBQSFlatHexRoundSquareTube
48Stainless - 3031/2"$0.49441/8" x 1"$0.94405/16"$0.52451/4"$0.11531/2"$0.9600
495/8"$0.76313/16" x 1-1/4"$2.51153/8"$0.57505/16"$0.21765/8"$1.6643
503/4"$1.09871/4" x 3/4"$0.82941/2"$0.73653/8"$0.25253/4"$1.6594
517/8"$1.67001/4" x 1"$0.99105/8"$0.86591/2"$0.45017/8"$3.5774
521"$1.97161/4" x 1-1/2"$1.55783/4"$1.51005/8"$0.69171"$3.0439
531-1/8"$2.49971/4" x 3"$2.88971"$2.39003/4"$0.98811-1/4"$5.8439
541-1/4"$3.09103/8" x 3/4"$1.13001-1/4"$4.04017/8"$1.35391-1/2"$9.0036
551-3/8"$3.76673/8" x 1"$1.36001-1/2"$3.11011"$1.76751-3/4"$11.8800
561-1/2"$4.36383/8" x 1-1/2"$2.04171-3/4"$7.86971-1/8"$2.25052"$13.8970
571-3/4"$7.18343/8" x 2"$2.90172"$10.59001-1/4"$2.77202-1/2"$23.9700
583/8" x 4"$5.74101-3/8"$3.0306
591/2" x 3/4"$2.17551-1/2"$3.5936
601/2" x 1"$1.78001-5/8"$3.8359
611/2" x 1-1/4"$2.43931-3/4"$4.4676
621/2" x 1-1/2"$2.32002"$5.7988
631/2" x 1-3/4"$2.72182-1/2"$9.0706
641/2" x 2"$3.1900
651/2" x 2-1/2"$4.6018
665/8" x 1-1/4"$5.3036
675/8" x 1-1/2"$3.3000
685/8" x 2"$4.4312
695/8" x 2-1/2"$9.3827
703/4" x 1"$2.0300
713/4" x 1-1/4"$3.3400
723/4" x 1-1/2"$3.9192
733/4" x 1-3/4"$8.2202
743/4" x 2"$5.3686
753/4" x 2-1/2"$6.5602
763/4" x 4"$10.3398
771" x 1-1/4"$4.4652
781" x 1-1/2"$5.1300
791" x 2"$6.6789
801" x 2-1/4"
811' x 2-1/2"$8.2871
821" x 3"$9.6500
831" x 3-1/2"$11.5836
841" x 6'$17.3600
851-1/4" x 2"$8.5280
861-1/2" x 2"$10.0104
872" x 4"$59.6400
88
89AngleBQSFlatHexRoundSquareTube
90Stainless - 3042" x 2" x 1/4"$4.20001/2"$0.49441/8" x 3/4"$0.78001-1/8"$1.59003/4" x .120"$0.8521
912-1/2" x 2-1/2" x 3/8"$5.05005/8"$0.76311/4" x 1/2"$0.7060
923/4"$1.09871/4" x 3/4"$0.7450
937/8"$1.67001/4" x 1"$1.0400
941"$1.97161/4" x 1-1/2"$0.8600
951-1/8"$2.49973/8" x 3/4"$1.1600
961-1/4"$3.09103/8" x 1"$1.2100
971-3/8"$3.76673/8" x 1-1/4"$1.8400
981-1/2"$4.36383/8" x 1-1/2"$1.8400
991-3/4"$7.18343/8" x 2"$1.3800
1003/8" x 3"$2.5500
1013/8" x 4"$3.6700
1023/8" x 5"$6.5100
1031/2" x 3/4"$1.0700
1041/2" x 1"$1.6770
1051/2" x 1-1/4"$3.6100
1061/2" x 1-1/2"$3.7000
1071/2" x 1-3/4"$2.7230
1081/2" x 2"$3.4000
1091/2" x 2-1/2"$3.7980
1101/2" x 3"$4.7500
1111/2" x 3-1/2"$8.2100
1125/8" x 3/4"$2.9200
1135/8" x 2-1/2"$7.3600
1143/4" x 1"$9.9410
1153/4" x 1-1/4"$4.1560
1163/4" x 1-1/2"$4.4200
1173/4" x 1-3/4"$3.8300
1183/4" x 2"$6.9000
1193/4" x 2-1/2"$4.8900
1203/4" x 3"$7.8800
1211" x 1-1/4"$4.4300
1221" x 1-1/2"$5.2900
1231" x 1-3/4"$6.7100
1241" x2"$5.7700
1251" x 2-1/2"$9.9410
1261" x 3"$9.2100
1271" x 3-1/2"$10.5500
1281-1/4" x 2"$5.8300
1291-1/2" x 2"$9.2100
1302" x 4"$21.8700
131
132AngleBQSFlatHexRoundSquareTube
133Stainless - 17-41/2" PH$0.8100
1343/4" PH$1.4600
1351/2" PH Annealed$0.9860
136
Master



Moline PO - SX 7100 New Revision.xlsx
BCD
1MaterialShapeSize
2Alum. - 6061Angle1/4" x 2"
3BrassFlat1/4" x 5"
4BronzeRound3/8" x 2"
5Carbon - 1018Square1/2" x 2"
6Carbon - 11441/2" x 6"
7Carbon - 12151" x 2"
8Plastic1" x 3-1/2"
9Stainless - 3031-1/2" x 2"
10Stainless - 3041-1/2" x 4"
11Stainless - 17-42" x 2-1/2"
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Tables
Cell Formulas
RangeFormula
B2:B11B2=LET(table,Master!B2:W136,a,INDEX(table,0,1),FILTER(a,a<>""))
C2:C5C2=LET(table,Master!B2:W136,f,INDEX(table,0,1),m,MATCH(Pricing!E2,f,0),TRANSPOSE(FILTER(INDEX(table,m-1,0),(INDEX(table,m,0)<>"")*(MOD(COLUMN(table)-COLUMN(f),3)=2))))
D2:D11D2=LET(table,Master!B2:W136,f,INDEX(table,0,1),m,MATCH(Pricing!E2,f,0),c,MATCH(Pricing!F2,INDEX(table,m-1,0),0),r,INDEX(table,m,c):INDEX(table,ROWS(table),c),b,MIN(IF(r="",ROW(r)-ROW(INDEX(table,1,1))+1,""))-1,INDEX(table,m,c):INDEX(table,b,c))
Dynamic array formulas.




Thank you again for the help!! Like I said, I know enough to be dangerous, but I would have NEVER figured this out on my own. Your help is very much appreciated. :)
 
Upvote 0
I just had to tweak the last formula a bit:

Book1
ABCDEFGHIJKLMNO
1Job #LineProductQty OrdMaterialShapeSizeTotal Inches for jobIn StockNeed to OrderMat'l $/"Mat'l EaLabor Hrs.Labor EaTotal Price Ea
212341Plate10Alum. - 6061Flat1" x 2"301.56864.7120132136.71
3Stainless - 304Flat1/4" x 1"1.04
4Stainless - 304Flat1/4" x 1"1.04
Pricing
Cell Formulas
RangeFormula
K2:K4K2=LET(table,Master!$B$2:$W$136,f,INDEX(table,0,1),m,MATCH(Pricing!E2,f,0),c,MATCH(Pricing!F2,INDEX(table,m-1,0),0),r,INDEX(table,m,c):INDEX(table,ROWS(table),c),b,MIN(IF(r="",ROW(r)-ROW(INDEX(table,1,1))+1,""))-1,v,INDEX(table,m,c):INDEX(table,b,c+1),VLOOKUP(G2,v,2,0))
L2:L4L2=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
N2:N4N2=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O4O2=IF(ISBLANK(D2)," ",L2+N2)
Cells with Data Validation
CellAllowCriteria
E2:G4List=Tables!A$2#
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
How to populate automatically a value depending on the previous drop down cell
Replies
3
Views
130
Joe4
Joe4
M
  • Question Question
Can excel return results that are calculated from separate items in a drop down list
2 3 4
Replies
33
Views
607
Peter_SSs
Peter_SSs
Z
  • Question Question
How to create a dynamic INDIRECT formula when dragging down
Replies
2
Views
165
wag97
W
J
  • Question Question
Filtering Results in List Box
Replies
2
Views
117
JohnS_323
J
A
  • Solved
Filter formula based on drop down BUT with an option for all data
Replies
7
Views
297
myall_blues
myall_blues

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

Share this page

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