Using Drop downs in creating an estimating worksheet.

Hi there,

If I am interpreting correctly, you want to pull the cost in column C from rows 35 and down.



You need to have the values in columns A and B exactly like the items in the list for the dropdowns.

Then you can use:

B21
=INDEX(C36:C44,INDEX(MATCH(B16&B17,A36:A44&B36:B44,0),0))

.

	A	B	C	D	E	F	G	H	I	J
16	Substrate	MDO		Substrates:		Thickness	Cost	Letter Ht. ( Inch)	Area
17	Thickness	12.5mm (1/2")		Acrylic Opaque White		2mm (1/16")	$25.00	1	1	sq.in
18	Letter Height (in.)	6		Acrylic Clear		3mm (1/8")	$30.00	2	4	sq.in
19	Text (Type here)	14		Acrylic Black		4.5mm (3/16")	$35.00	3	9	sq.in
20				PVC White		6mm (1/4")	$50.00	4	16	sq.in
21	Material Cost			PVC Black		9mm (3/8")	$75.00	5	25	sq.in
22				Dibond White		12.5mm (1/2")	$100.00	6	36	sq.in
23				Dibond Black		19mm (3/4")	$120.00	7	49	sq.in
24				Dibond Brushed Silver		1"	$150.00	8	64	sq.in
25				Gatorfoam White		1.5"	$175.00	9	81	sq.in
26				Gatorfoam Black		2"	$225.00	10	100	sq.in
27				MDO (Prefinished)				12	144	sq.in
28				Octolux				14	196	sq.in
29								16	256	sq.in
30								18	324	sq.in
31								20	400	sq.in
32								22	484	sq.in
33								24	576	sq.in
34								28	784	sq.in
35	Substrate (4'x8')	Thickness	Cost	M/U	Price	Price per Sq. In.		32	1024	sq.in
36	MDO (Prefinished)	12.5mm (1/2")	$100.00	1.6	$160.00	$0.03
37
38
39
40
41
42
43
44	Cols A & B are DATA VALIDATION		Cols C, D & F are Formulas
45
46				Col D is manual input
47

CORRECTION : Cols C, E & F are formulas

In B21

=AGGREGATE(14,6,$F$36:$F$37/(($A$36:$A$37=B16)*($B$36:$B$37=B17)),1)*B18*B18*B19

NOTE: A36:A37 values should exactly match with values in D17:D28
NOTE: B36:B37 values should exactly match with values in E17:E29