Dependancy Formula

Hi everyone, I am working on a formula that I just cannot seem to automate and it is starting to aggravate me.

The basic premise is that I have two sheets in a workbook, sheet one containing Employee info, such as rate of renumeration, overtime rate and whatnot.

What I am trying to do is automate sheet two, currently I have the basic info automated based on employee ID, but what I want to do is automate the calculation of payment based on hours based on the employee ID. (It sounds more complicated than it is.)

So in any given row I would have(Among other things):

EMP ID NAME HOURS (REG) HOURS (OT) GROSS PAY
01 Excel 80 12 (This is what I want to automate based on employee ID and formulating pay by calculating hours and appropriate pay rate on sheet 1)

This is where I get stumped, because, sure I could write a simple equation to calculate hours * pay rate, but this doesnt remain static and requires input for each calculation, thereby negating the purpose of automation.


Any help?

If more detail is needed please let me know.

I think a sample workbook showing some expected results and the logic behind it would make it a bit easier to follow. Please post a sample workbook.

Sample of the working area I am using with some of the cells. So in this instance I have Employee 1 and the Name field is populated based on =IF(ISBLANK(C4),"",INDEX(EmployeeInfo!B:B,MATCH($C4,EmployeeInfo!A:A,0)))

What I want to be able to do is to automate the GROSS PAY cell to automatically calculate pay for different employees (At different rates) based on employee ID. The pay rates are in a seperate sheet.
I want to do this to let several employees be tracked at the same time with multiple rates of pay without having to alter the formulas. (Currently the gross pay cell is calculated by a formula that must be altered for each individual. =((L4*EmployeeInfo!I5)+(Q4*EmployeeInfo!J5)) Where each Pay rate, both regular and OT are represented by column I and J respectively.

I think you need a VLOOKUP formula then.

e.g.

=L4*VLOOKUP(B4,EmployeeInfo!H:J,2,0)+Q4*VLOOKUP(B4,EmployeeInfo!H:J,3,0)

where it assumed that the Employee names are listed in column B and the the table in EmployeInfo sheet lists the employees in column H, with I and J being the 2nd and 3rd columns in that table (that is what the 2 and 3 in the VLOOKUP mean).

Not sure if this helps. The VLOOKUP function wasn't working for me and I am not feeling all that well so trying to retool it for the desired purpose is giving me a giant migrane.

As you can see currently only one employee ID can be used as I have to continually change the formulas and the request is for automation.

Thanks for all the help so far

In this case just change the table range in the VLOOKUPs and the references to where the name really is in your active sheet

=L4*VLOOKUP(D4,EmployeeInfo!B:J,8,0)+Q4*VLOOKUP(D4,EmployeeInfo!B:J,9,0)

Did this work?

Sorry, I ended up changing the values since for whatever reason, the string you supplied would not calculate the data properly.
After about an hour of playing with values and string preparation, I ended up using =(VLOOKUP(C:C,EmployeeInfo!1:65536,9,FALSE)*L10)+(VLOOKUP(C:C,EmployeeInfo!1:65536,10,FALSE)*Q10) to finalise the whole equation. It works like a charm and now properly reflects the pay rate of the appropriate individual.

Thanks for pointing me in the right direction.

A.Sev.

You don't need to reference the whole column C.. and definitely don't need to reference the whole of EmploymentInfo sheet

I would try:

=(VLOOKUP(C2,EmployeeInfo!A:J,9,FALSE)*L10)+(VLOOKUP(C2,EmployeeInfo!A:J,10,FALSE)*Q10)