On-Call schedule

[Sabine]

We have an on-call schedule for support calls and for each day there is a
separate primary, backup, supervisor, and manager responsible for that day.

Date 4/2/2006 4/3/2006 4/4/2006 4/5/2006
Primary Joe Smith
Backup Tom Jones Joe Smith
Supervisor
Manager

Each function (primary, backup, supervisor, and manager) has a different
hourly rate and there are about 10 different people handling these functions
during each two-week period. Right now, we manually key in the daily rate for
each of these employee into a spreadsheet after looking up what function they
had on each of the days

Joe Smith Tom Jones John Tee Greg Miller Jane Walker
04/02/06 60.00 60.00
04/03/06 80.00
04/04/06 60.00
04/05/06
04/06/06
04/07/06
04/08/06
04/09/06

What would be the best way to set this up - vlookups didn't seem to do the
job (too many conditions).

I'd really appreciate any help on this.
Thanks
Sabine

 

[Pete_UK]

I would suggest that you arrange your first sheet in a similar way as
the second, with the dates going down the page and five columns - date,
primary, backup, supervisor, and manager. If you want to change the
layout of an existing sheet to this, then you can transpose the data
quite easily.

In a third sheet you can have a simple table listing the hourly rate
for each of the four functions, i.e.:

Primary 60.00
Backup 80.00
Supervisor 100.00
Manager 120.00

Obviously, I'm guessing the rates, but assume this table occupies A1:B4
of Sheet3.

In your second sheet, with the first date being the same as the first
date in Sheet1, enter this formula in cell B2:

=IF(ISNA(MATCH(B$1,Sheet1!$B2:$E2,0)),"",INDEX(Sheet3!$A$1:$B$4,MATCH(B$1,Sheet1!$B2:$E2,0),2))

This can be copied across for as many names as you have. Then highlight
these cells and copy down for as many dates as you have.

This will give you the four rates as appropriate, and leave blanks
where a person did not work that day - you can change the "" in the
middle of the formula to 0 if you prefer.

Hope this helps.

Pete

 

[Sabine]

Thanks a lot. This worked and will save us a buch of time.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete