Changing values in multiple cells based off of drop down list.

[RamDodgerLaker]

Hello, I work as a proposal manager and need some help with my estimator tool I am building. I basically need the pricing for labor to change based off of what state is selected. For example if I select California in my estimator I need the price for a tech 1 to show 90k and the price for a tech 2 to show 100k but if I select West Virginia I need the price for a tech 1 to show 60k and for a tech 2 to show 70k.

 

[Eric W]

Welcome to the MrExcel forum!

You didn't give us a lot of detail, but maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	State	Tech1	Tech2		State		Estimate
2	California	90000	100000		West Virginia					Extended
3	West Virginia	60000	70000				Worker	Rate	Hours
4	Alaska	50000	75000				Tech1	60000	6
5	Oregon	70000	80000				Tech2	70000	2
6
7							Parts	Unit Cost	Count
8							Widget	100	2
9							Gizmo	75.32	3
10
11							Total
12
Sheet1

Cell Formulas
Range	Formula
H4:H5	H4	=SUMPRODUCT($B$2:$C$5*(G4=$B$1:$C$1)*($E$2=$A$2:$A$5))

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=A2:A5

First, build a table like I did in A1:C5. Then you can put in a Data Validation drop-down, using A2:A5 as the list. This lets you pick a state. Then once you have the state, there are a lot of ways to extract the right rate for the state and title. I put one example in H4:H5. I didn't go much further, since the rates you mentioned must be annual salaries, and not hourly rates, so I couldn't picture how your estimator actually looks. But this should give you an idea of what you can do.

Hope this helps!