Multiple lookup values and adding multiple rates across together

[ssolomon]

I’m 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 I’m 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 doesn’t 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

 

[Mike H.]

I think the reason no one has replied to your is that your question is too
long. I think the way to do this would be to read the data into an array or
two and then just write your report out after doing the calculations in the
macro. I'd be happy to get you started but don't understand what you want to
do.

 

[Mike H.]

Okay, I think I figured out what you want to do. To do this the easy way
would be to create an array. The first element would be the concatenation of
Employee & Dept. so that you create a unique "Employee" based on each
combination. Then you would read all that the forecast data into an array,
then add the actual data to the array, and then write out the report. In my
opinion, this would be way easier in the long run to set up and maintain.
When I have a bit more time, I'll work on designing this and send to you.

 

[ssolomon via OfficeKB.com]

Looking back at the message, your probably correct..way too long.

I'm going to have to start studying about array's as I'm new to this. I
appreciate the help!

Thanks,

Steve

 

[Mike H.]

I have no idea what the salary base percentages are? I don't understand what
is in the tracking sheet. Please advice.

 

[ssolomon via OfficeKB.com]

Mike,

We have 2 types of % that we base our work on. The first is a 'salary based
utilization' percentage and the second is an 'hours based utilization'
percentage.

When we calculate the hours based utilization, I just divide the hours worked
by the hours available. The salary based utilization is calculated the same
way.

If you do these calculations for 1 person, the % are the same. When you add
an entire column of hours worked and add the entire column of hours available,
I use these totals and divide to get the utilization % for the entire set of
employees.

But if you do the same and it's salary based, then the utilization % is
generally lower. (Our higher payed employees generally work less hours on
specified projects (thus lowering their utilization) and more on business
development (which does not count toward actual hours worked).

I have been able to create the functions to pull an individual who works in
different departments or with different rates. This is what I worked out
with just 3 people (don't need for anyone to view another person's salary
rate):

=((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))

This is for our smallest group. There are 9 other rows that are filled in
with 'new hire' for the name and their salary is all set to $0.00. I want
the function to add up those that are in the group, but I get a #REF! error
message in the cell. (I just added 9 more of one of the calculations to see
if it would work). I guess the hardest part is that this is the smallest
group. I have groups that contain 25 employees with room for 15 more new
hires. Any ideas?

Steve

Now, I can't figure out how to come up with all the 'salary based'
utilization totals for an entire group of people.

I have no idea what the salary base percentages are? I don't understand what
is in the tracking sheet. Please advice.

I’m 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:

[quoted text clipped - 79 lines]

Steve