Multiple Dependent Drop Down Lists

[theom893]

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?

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Type	Shape	Size		Formula 1	Formula 2	Formula 3		Type	Shape	Size
2	Wood	square	1		Wood	square	1		Glass	round	2
3	Wood	square	2		Metal	round	2
4	Wood	round	1		Plastic	toric	3
5					Glass
6	Metal	hexagonal	1
7	Metal	octagonal	1
8
9	Plastic	square	1
10	Plastic	square	2
11
12	Glass	square	1
13	Glass	square	2
14	Glass	square	3
15	Glass	round	1
16	Glass	round	2
17	Glass	round	3
18	Glass	toric	4
19
20
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(FILTER(A2:A20,A2:A20<>""))
F2:F4	F2	=UNIQUE(FILTER(B2:B20,A2:A20=I2))
G2:G4	G2	=UNIQUE(FILTER(C2:C20,(A2:A20=I2)*(B2:B20=J2)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2:K2	List	=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!

 

[theom893]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2				Angle	Price/Inch		BQS	Price/Inch		Flat	Price/Inch		Hex	Price/Inch		Round	Price/Inch		Square	Price/Inch		Tube	Price/Inch
3		Alum. - 6061		2" x 3" x 3/8"	$1.3890					1/4" x 2"	$0.6920					1"	$0.4850		1-1/2"	$1.8500
4										1/4" x 5"	$1.3156
5										3/8" x 2"	$0.6500
6										1/2" x 2"	$0.8220
7										1/2" x 6"	$3.5100
8										1" x 2"	$1.5686
9										1" x 3-1/2"	$2.1900
10										1-1/2" x 2"	$2.0800
11										1-1/2" x 4"	$4.1700
12										2" x 2-1/2"	$3.2000
13
14				Angle			BQS			Flat			Hex			Round			Square			Tube
15		Brass								3/4" x 1"	$2.7507
16
17				Angle			BQS									Round			Square			Tube
18		Bronze														5/8"	$2.4560
19																1"	$5.0277
20																1-1/4"	$7.0223
21
22				Angle			BQS			Flat			Hex			Round			Square			Tube
23		Carbon - 1018								3/8" x 3/4"	$0.4970					1/2"	$0.2710		3/4"	$0.5024
24										3/8" x 1"	$0.5600					3/4"	$0.3815
25																1-1/4"	$0.7850
26																1-3/8"	$0.9071
27																2"	$1.8405
28																2-1/4"	$2.2400
29																3"	$3.9958
30
31				Angle			BQS			Flat			Hex			Round			Square			Tube
32		Carbon - 1144														1"	$0.2710
33																1-1/8"	$0.3815
34																1-1/4"	$0.7850
35																1-1/2"	$0.9071
36																1-5/8"	$1.8405
37																2"	$2.2400
38																2-1/2"	$3.9958
39
40				Angle			BQS			Flat			Hex			Round			Square			Tube
41		Carbon - 1215														3/4"	$0.4900
42																2"	$1.9400
43
Master

Moline PO - SX 7100 New Revision.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Job #	Line	Product	Qty Ord	Material	Shape	Size	Inches	Stock	Order	Mat'l $/"	Mat'l Ea	Line Labor	Labor Ea	Price 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
Range	Formula
N2:N21	N2	=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O21	O2	=IF(ISBLANK(D2)," ",L2+N2)
L2:L21	L2	=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))

Cells with Data Validation
Cell	Allow	Criteria
E2:E21	List	=Master!#REF!

 

[Eric W]

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
	AA	AB	AC
1	Formula 1	Formula 2	Formula 3
2	Alum. - 6061	Flat	1/2"
3	Brass	Round	3/4"
4	Bronze	Square	1-1/4"
5	Carbon - 1018		1-3/8"
6	Carbon - 1144		2"
7	Carbon - 1215		2-1/4"
8			3"
9
10
Master

Cell Formulas
Range	Formula
AA2:AA7	AA2	=LET(table,B2:W100,a,INDEX(table,0,1),FILTER(a,a<>""))
AB2:AB4	AB2	=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:AC8	AC2	=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Job #	Line	Product	Qty Ord	Material	Shape	Size	Inches	Stock	Order	Mat'l $/"	Mat'l Ea	Line Labor	Labor Ea	Price Ea
2					Carbon - 1018	Round	1-3/8"				1		20
Pricing

Cell Formulas
Range	Formula
L2	L2	=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
N2	N2	=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2	O2	=IF(ISBLANK(D2)," ",L2+N2)

Cells with Data Validation
Cell	Allow	Criteria
E2:G2	List	=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.

 

[theom893]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Job #	Line	Product	Qty Ord	Material	Shape	Size	Total Inches for job	In Stock	Need to Order	Mat'l $/"	Mat'l Ea	Labor Hrs.	Labor Ea	Total Price Ea
2	1234	1	Plate	10	Alum. - 6061	Flat	1" x 2"	30				0	20.00	$132.00	$132.00
3					Stainless - 304	Flat	1/4" x 1"
4					Stainless - 304	Flat	1/4" x 1"
5					Stainless - 304	Flat	1/4" x 1"
6					Stainless - 304	Flat	1/4" x 1"
7					Stainless - 304	Flat	1/4" x 1"
8					Stainless - 304	Flat	1/4" x 1"
9					Stainless - 304	Flat	1/4" x 1"
10					Stainless - 304	Flat	1/4" x 1"
11					Stainless - 304	Flat	1/4" x 1"
12					Stainless - 304	Flat	1/4" x 1"
13					Stainless - 304	Flat	1/4" x 1"
14					Stainless - 304	Flat	1/4" x 1"
15					Stainless - 304	Flat	1/4" x 1"
16					Stainless - 304	Flat	1/4" x 1"
17					Stainless - 304	Flat	1/4" x 1"
18					Stainless - 304	Flat	1/4" x 1"
19					Stainless - 304	Flat	1/4" x 1"
20					Stainless - 304	Flat	1/4" x 1"
21					Stainless - 304	Flat	1/4" x 1"
22					Stainless - 304	Flat	1/4" x 1"
23					Stainless - 304	Flat	1/4" x 1"
24					Stainless - 304	Flat	1/4" x 1"
25					Stainless - 304	Flat	1/4" x 1"
26					Stainless - 304	Flat	1/4" x 1"
Pricing

Cell Formulas
Range	Formula
N2:N26	N2	=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O26	O2	=IF(ISBLANK(D2)," ",L2+N2)
L2:L26	L2	=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))

Cells with Data Validation
Cell	Allow	Criteria
E2:E26	List	=Tables!$B$2:$B$11
F2:F26	List	=Tables!$C$2:$C$8
G2:G26	List	=Tables!$D$2:$D$62

Moline PO - SX 7100 New Revision.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2			Angle			BQS			Flat			Hex			Round			Square			Tube
3	Alum. - 6061		2" x 3" x 3/8"	$1.3890					1/4" x 2"	$0.6920					1"	$0.4850		1-1/2"	$1.8500
4									1/4" x 5"	$1.3156
5									3/8" x 2"	$0.6500
6									1/2" x 2"	$0.8220
7									1/2" x 6"	$3.5100
8									1" x 2"	$1.5686
9									1" x 3-1/2"	$2.1900
10									1-1/2" x 2"	$2.0800
11									1-1/2" x 4"	$4.1700
12									2" x 2-1/2"	$3.2000
13
14			Angle			BQS			Flat			Hex			Round			Square			Tube
15	Brass								3/4" x 1"	$2.7507
16
17			Angle			BQS									Round			Square			Tube
18	Bronze														5/8"	$2.4560
19															1"	$5.0277
20															1-1/4"	$7.0223
21
22			Angle			BQS			Flat			Hex			Round			Square			Tube
23	Carbon - 1018								3/8" x 3/4"	$0.4970					1/2"	$0.2710		3/4"	$0.5024
24									3/8" x 1"	$0.5600					3/4"	$0.3815
25															1-1/4"	$0.7850
26															1-3/8"	$0.9071
27															2"	$1.8405
28															2-1/4"	$2.2400
29															3"	$3.9958
30
31			Angle			BQS			Flat			Hex			Round			Square			Tube
32	Carbon - 1144														1"	$0.2710
33															1-1/8"	$0.3815
34															1-1/4"	$0.7850
35															1-1/2"	$0.9071
36															1-5/8"	$1.8405
37															2"	$2.2400
38															2-1/2"	$3.9958
39
40			Angle			BQS			Flat			Hex			Round			Square			Tube
41	Carbon - 1215														3/4"	$0.4900
42															2"	$1.9400
43
44			Angle			BQS			Flat			Hex			Round			Square			Tube
45	Plastic
46
47			Angle			BQS			Flat			Hex			Round			Square			Tube
48	Stainless - 303					1/2"	$0.4944		1/8" x 1"	$0.9440		5/16"	$0.5245		1/4"	$0.1153		1/2"	$0.9600
49						5/8"	$0.7631		3/16" x 1-1/4"	$2.5115		3/8"	$0.5750		5/16"	$0.2176		5/8"	$1.6643
50						3/4"	$1.0987		1/4" x 3/4"	$0.8294		1/2"	$0.7365		3/8"	$0.2525		3/4"	$1.6594
51						7/8"	$1.6700		1/4" x 1"	$0.9910		5/8"	$0.8659		1/2"	$0.4501		7/8"	$3.5774
52						1"	$1.9716		1/4" x 1-1/2"	$1.5578		3/4"	$1.5100		5/8"	$0.6917		1"	$3.0439
53						1-1/8"	$2.4997		1/4" x 3"	$2.8897		1"	$2.3900		3/4"	$0.9881		1-1/4"	$5.8439
54						1-1/4"	$3.0910		3/8" x 3/4"	$1.1300		1-1/4"	$4.0401		7/8"	$1.3539		1-1/2"	$9.0036
55						1-3/8"	$3.7667		3/8" x 1"	$1.3600		1-1/2"	$3.1101		1"	$1.7675		1-3/4"	$11.8800
56						1-1/2"	$4.3638		3/8" x 1-1/2"	$2.0417		1-3/4"	$7.8697		1-1/8"	$2.2505		2"	$13.8970
57						1-3/4"	$7.1834		3/8" x 2"	$2.9017		2"	$10.5900		1-1/4"	$2.7720		2-1/2"	$23.9700
58									3/8" x 4"	$5.7410					1-3/8"	$3.0306
59									1/2" x 3/4"	$2.1755					1-1/2"	$3.5936
60									1/2" x 1"	$1.7800					1-5/8"	$3.8359
61									1/2" x 1-1/4"	$2.4393					1-3/4"	$4.4676
62									1/2" x 1-1/2"	$2.3200					2"	$5.7988
63									1/2" x 1-3/4"	$2.7218					2-1/2"	$9.0706
64									1/2" x 2"	$3.1900
65									1/2" x 2-1/2"	$4.6018
66									5/8" x 1-1/4"	$5.3036
67									5/8" x 1-1/2"	$3.3000
68									5/8" x 2"	$4.4312
69									5/8" x 2-1/2"	$9.3827
70									3/4" x 1"	$2.0300
71									3/4" x 1-1/4"	$3.3400
72									3/4" x 1-1/2"	$3.9192
73									3/4" x 1-3/4"	$8.2202
74									3/4" x 2"	$5.3686
75									3/4" x 2-1/2"	$6.5602
76									3/4" x 4"	$10.3398
77									1" x 1-1/4"	$4.4652
78									1" x 1-1/2"	$5.1300
79									1" x 2"	$6.6789
80									1" x 2-1/4"
81									1' x 2-1/2"	$8.2871
82									1" x 3"	$9.6500
83									1" x 3-1/2"	$11.5836
84									1" x 6'	$17.3600
85									1-1/4" x 2"	$8.5280
86									1-1/2" x 2"	$10.0104
87									2" x 4"	$59.6400
88
89			Angle			BQS			Flat			Hex			Round			Square			Tube
90	Stainless - 304		2" x 2" x 1/4"	$4.2000		1/2"	$0.4944		1/8" x 3/4"	$0.7800					1-1/8"	$1.5900					3/4" x .120"	$0.8521
91			2-1/2" x 2-1/2" x 3/8"	$5.0500		5/8"	$0.7631		1/4" x 1/2"	$0.7060
92						3/4"	$1.0987		1/4" x 3/4"	$0.7450
93						7/8"	$1.6700		1/4" x 1"	$1.0400
94						1"	$1.9716		1/4" x 1-1/2"	$0.8600
95						1-1/8"	$2.4997		3/8" x 3/4"	$1.1600
96						1-1/4"	$3.0910		3/8" x 1"	$1.2100
97						1-3/8"	$3.7667		3/8" x 1-1/4"	$1.8400
98						1-1/2"	$4.3638		3/8" x 1-1/2"	$1.8400
99						1-3/4"	$7.1834		3/8" x 2"	$1.3800
100									3/8" x 3"	$2.5500
101									3/8" x 4"	$3.6700
102									3/8" x 5"	$6.5100
103									1/2" x 3/4"	$1.0700
104									1/2" x 1"	$1.6770
105									1/2" x 1-1/4"	$3.6100
106									1/2" x 1-1/2"	$3.7000
107									1/2" x 1-3/4"	$2.7230
108									1/2" x 2"	$3.4000
109									1/2" x 2-1/2"	$3.7980
110									1/2" x 3"	$4.7500
111									1/2" x 3-1/2"	$8.2100
112									5/8" x 3/4"	$2.9200
113									5/8" x 2-1/2"	$7.3600
114									3/4" x 1"	$9.9410
115									3/4" x 1-1/4"	$4.1560
116									3/4" x 1-1/2"	$4.4200
117									3/4" x 1-3/4"	$3.8300
118									3/4" x 2"	$6.9000
119									3/4" x 2-1/2"	$4.8900
120									3/4" x 3"	$7.8800
121									1" x 1-1/4"	$4.4300
122									1" x 1-1/2"	$5.2900
123									1" x 1-3/4"	$6.7100
124									1" x2"	$5.7700
125									1" x 2-1/2"	$9.9410
126									1" x 3"	$9.2100
127									1" x 3-1/2"	$10.5500
128									1-1/4" x 2"	$5.8300
129									1-1/2" x 2"	$9.2100
130									2" x 4"	$21.8700
131
132			Angle			BQS			Flat			Hex			Round			Square			Tube
133	Stainless - 17-4														1/2" PH	$0.8100
134															3/4" PH	$1.4600
135															1/2" PH Annealed	$0.9860
136
Master

Moline PO - SX 7100 New Revision.xlsx
	B	C	D
1	Material	Shape	Size
2	Alum. - 6061	Angle	1/4" x 2"
3	Brass	Flat	1/4" x 5"
4	Bronze	Round	3/8" x 2"
5	Carbon - 1018	Square	1/2" x 2"
6	Carbon - 1144		1/2" x 6"
7	Carbon - 1215		1" x 2"
8	Plastic		1" x 3-1/2"
9	Stainless - 303		1-1/2" x 2"
10	Stainless - 304		1-1/2" x 4"
11	Stainless - 17-4		2" 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
Range	Formula
B2:B11	B2	=LET(table,Master!B2:W136,a,INDEX(table,0,1),FILTER(a,a<>""))
C2:C5	C2	=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:D11	D2	=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.

 

[Eric W]

I just had to tweak the last formula a bit:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Job #	Line	Product	Qty Ord	Material	Shape	Size	Total Inches for job	In Stock	Need to Order	Mat'l $/"	Mat'l Ea	Labor Hrs.	Labor Ea	Total Price Ea
2	1234	1	Plate	10	Alum. - 6061	Flat	1" x 2"	30			1.5686	4.71	20	132	136.71
3					Stainless - 304	Flat	1/4" x 1"				1.04
4					Stainless - 304	Flat	1/4" x 1"				1.04
Pricing

Cell Formulas
Range	Formula
K2:K4	K2	=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:L4	L2	=IF(ISBLANK(D2)," ",(ROUND(H2/D2*K2,2)))
N2:N4	N2	=IF(ISBLANK(D2)," ",ROUND(M2*66/D2,2))
O2:O4	O2	=IF(ISBLANK(D2)," ",L2+N2)

Cells with Data Validation
Cell	Allow	Criteria
E2:G4	List	=Tables!A$2#