Invoice formulas. VLOOKUP or no?

[WongBob]

I am trying to build a new invoice spreadsheet for the company I work for. We are going from a day rate system to hourly. What I am trying to do is set it up with one drop down menu for our 4 level pay scale and another with a list of the dozen or so companies we work for with each of those companies being billed at a different rate. The kicker is that depending on the company our guys work for, the rates they bill will change. There is also a changing bonus to bring the total charge up to X amount for a daily total depending on regular time or overtime hours. On top of that there is a modifier that needs to be added per company because the pay grade deduction is not equal across all companies.

Basically I need the following

A(Pay Grade_Dropdown).........B(Modifier)..................C(Company_Dropdown)..........D(Hours).............E(Labor Total From Hours)............F(Bonus)..........................G(Total)
Supervisor -$100 Mickey -$50 Mickey $800 12 $240 (Random Labor Rates) =SUM(A1:C1)-E1 =SUM(E1+F1)
Lead 1 -$200 Goofy $100 Goofy $1000 12 $360 =SUM(A2:C2)-E2 =SUM(E2+F2)
Lead 2 -$300 Pluto -$25 Pluto $500 12 $180 =SUM(A3:C3)-E3 =SUM(E3+F3)
Helper -$400 Donald $25 Donald $650 12 $300 =SUM(A4:C4)-E4 =SUM(E4+F4)


Now, from that it would be simple if the dollar amounts in A:C were on the front page of the spreadsheet, but they can not be. I need to be able to select "Lead 2" and "Mickey" from a dropdown menu, enter hours, and the spreadsheet grab the $800 for Mickey, subtract the $300 for Lead 2 deduction, and also the add in the modifier. The total of the A:C references needs to be subtracted from the E column and entered into the F column as the bonus. The sum of E and F will be the days labor.

Anyone have any idea how I can made this happen?

Thanks,
Wade

 

[WongBob]

Re: Need help with invoice formulas. VLOOKUP or no?

Couldn't find an edit post button to fix what I was trying to do in the OP.

A(Pay Grade_Dropdown).........B(Modifier)..................C(Company_Dropdown)..........D(Hours).............E(Labor Total From Hours)............F(Bonus)..........................G(Total)
Supervisor -$100....................Mickey -$50 ...............Mickey $800 .........................12......................$240 (Random Labor Rates).........=SUM(A1:C1)-E1...............=SUM(E1+F1)
Lead 1 -$200..........................Goofy $100.................Goofy $1000.........................12......................$360.........................................=SUM(A2:C2)-E2...............=SUM(E2+F2)
Lead 2 -$300..........................Pluto -$25...................Pluto $500............................12......................$180.........................................=SUM(A3:C3)-E3...............=SUM(E3+F3)
Helper -$400..........................Donald $25..................Donald $650.........................12......................$300.........................................=SUM(A4:C4)-E4...............=SUM(E4+F4)