Im trying to determine the function(s) that would allow me to do several
things. First I will give a simple example of what I have:
€¢ I have two sheets, one has salary data €“ you will notice that an individual
can be in two different departments with different rates. This can occur
when an employee works part of the month in one department and in another
department for the remainder of the month. The rates would also vary
depending upon the location of the department (or if the employee gets a
raise and moves to a different department)
€¢ The second sheet has the main tracking data.
€¢ The third sheet is a report that is sent by corporate hq. It would have
everyone broken down by department and give totals for each (in excel 2007
format also).

Now here is what Im trying to do:
1. I want to pull the hours available and hours worked from the Corporate
Work Report and put it in the tracking sheet for each person. I have created
a separate sheet that each department fills in with their projections for
hours worked etc and the function looks like this (only using 1 lookup value
(not 2):

=(VLOOKUP(B20,Arlington!$A$4:$O$18,15))

2. Salary based % and hours based % are going to be identical for an
individual, but when you total an entire group, the salary based % is
generally lower. (An individual making big money who doesnt work that many
hours affects the salary based % more) Hours based is easy as I would add up
the total hours worked and then divide by the total hours available. But to
generate the salary based % for an entire group (150+ employees), I would
have to take the hours worked X hourly rate and the hours available X the
hourly salary rate for every individual in the group and get a total of the
each $ figure (Hours worked and hours available) to perform the division and
determine the %.
3. I have created a line in the salary sheet that shows the ID as €˜0 and for
the name it is €˜new hire. This allows me to just add an individual when
needed and when I put the ID in the tracking sheet, information will
automatically fill out the remaining data. So when I do the group totals in
the tracking sheet, it will include extra blank lines (that have no value) to
allow for new people that get added, without having to insert and work the
formulas again.


Salary Sheet €“

A B C D
1 ID Department Name Hourly Salary
2 123456 0125 Smith,Bob 43.6879
3 123456 0126 Smith,Bob 46.2599
4 004689 1228 Jones,Darren 38.2145

Tracking Sheet €“

A B C D E F G
1 ID Dept Name Hours Available Hours Worked Hours based % Salary based %
2 123456 0125 Smith,Bob 100 95
3 123456 0126 Smith,Bob 78 70
4 004689 1228 Jones,Darren 178 164
Group Total 356 329

Corporate Work Report Sheet €“

A B C D E F G H
1 ID Dept Name Monthly Work Hours Holiday Time Off Available Hrs Hours Worked
2 123456 0125 Smith,Bob 108 8 8 92 88
3 006589 0125 Mack,Sarah 184 8 32 144 139.5
4 123456 0126 Smith,Bob 78 0 0 78 76.5
Group Total 370 16 40 314 304

I know this is alot of info, but I have used simple VLOOKUP formulas to pull
in most of the data, but when I realized that someone could have different
rates depending upon the department (or if there is a salary increase), the
having the VLOOPUP reference one lookup value would not work. When I total
for an entire group, I created a function with the VLOOKUP and for just 3
people, it was 2 ½ lines long, so I I were to do this for 30 people or more,
it would be so long that it seems that there would be an easier way. Here is
the function:

=((VLOOKUP(B6,'Salary Data'!$A$2:$E$90,5)*H6)+(VLOOKUP(B7,'Salary Data'!$A$2:
$E$90,5)*H7)+(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*H8))/((VLOOKUP(B6,
'Salary Data'!$A$2:$E$90,5)*G6)+(VLOOKUP(B7,'Salary Data'!$A$2:$E$90,5)*G7)+
(VLOOKUP(B8,'Salary Data'!$A$2:$E$90,5)*G8))

If anyone can give me some tips, it sure would help.

Steve