Using a drop down menu that contains charge codes to change values

[kmick37]

Hello. Looking for some guidance on something.
So i am creating a LEM template and trying to make it user friendly to save time/errors.

So below youll see under craft i have "Supt", well that i have created a drop down so i can select any charge code, and it will apply to the "totals show ST (Straight time) OT (Overtime) and DT (Double time)" THEN, it will auto populate the far right totals into a dollar amount.

So when i put 8 2 2 for hours, at this moment itll auto populate to the far right, but i have to change the formula to include the dollar amount on my Sheet2 data table. How would i make it so when i select a specific charge code, itll change that part in the formula where it shows dollar totals?

Right now my formula is simple, =(G14+J14+M14+P14+S14+V14+Y14)*85.5. But i would like that amount to change based on dollar code for each line.

WO #

WO#25006523

WO#2500654

WO#25006523

WO#2500654

WO#25006523

WO#2500654

WO#25006523

WO#2500654

Total

Name

ID

Shift

Craft

OT meal

Travel

ST

OT

DT

ST

OT

DT

ST

OT

DT

ST

OT

DT

ST

OT

DT

ST

OT

DT

ST

OT

DT

ST

OT

DT

Total

ST

OT

DT

Travel

OT meal

name

UNKNSH123

D/S

SUPT

1

2

8

2

2

14​

$684.00​

$237.50​

$306.30​

$241.66​

$25.00​

name

345345

D/S

SUPT

1

8​

2​

2​

12​

$600.56​

$209.76​

$269.50​

$0.00​

$25.00​

name

5345345

D/S

SUPT

name

345345

D/S

SUPT

name

UNKNSH123

D/S

SUPT

name

UNKNSH123

D/S

SUPT

 

[kmick37]

I might add, that along with the Craft charge code, the dollar amount for totals for labour costs, will change with DS or NS. So id like DS to change the dollar amount per hour but also and/or the charge code.