Employee Scheduling Worksheet

[karyn]

I have been using an excel workbook to create my employees schedules. I have
8 worksheets in the workbook; one for each day of the week and the last, a
roll-up of all the schedules. In the individual daily worksheets I have the
columns labels am through 11:30 PM and the rows as the employee names. I
manually enter X's through the times they are scheduled to work. The roll-up
sheets has the columns as the day of the week and the row as the employee
name with the cells as the time frame they are working (i.e. 8:00-1630) I was
wondering if there is a formula or a macro that will return the times
automatically on the roll-up sheet. I can send the attachment I have been
using.
Thanks

 

[T. Valko]

Here's an idea to get you started.

...........A..........B.........C..........D..........E
1..................800......830.......900.......930
2......Joe....................X...........X..........X
3......Sue........X.........X...........X.............
4......Tia..................................X...........X

In your real file B1:E1 would be true Excel time values.

...........A..........B..........C
9.................Start.......End
10.....Sue........................
11.....Joe........................
12.....Tia........................

Enter this formula in B10:

=INDEX(B$1:E$1,MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0))

Enter this formula in C10:

=INDEX(B$1:E$1,MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0)))

Select both B10 and C10 and copy down to B12:C12

...........A..........B..........C
9.................Start.......End
10.....Sue.....800.......900
11.....Joe.....830........930
12.....Tia......900.......930

If an employee did not work that day the formulas will return #N/A errors. I
would just let that happen then use conditional formatting to hide them.

 

[karyn]

This only works if all the information is on the same worksheet, how do i get
this formula when the info is on another worksheet?

 

[Shane Devenshire]

Show us a sample of the ouput area you would like, I'm not clear what you
want? I cell summing the total time of an employee on a given day or? It
might also help if you showed us a sample of your data layout on each tab.

For example, suppose your data sheets are in 1/2 increments running from
B1:Z1 with John's data on row 2 in all sheets. Then the following formula
would give John's total hours:

=COUNTA(Mon:Sun!B2:Z2)*2

 

[karyn]

First let me thank you all of all your help....Ok I figured out the formaula
to return the times, just as Biff stated. So my roll-up worksheet looks like
such.

A B C
My question is, is there any way to return a value of OFF rather then the
error #N/A when they aren't sceduled to work?

 

[T. Valko]

When a person if off is their name not listed on the daily sheets or, their
name is listed but there are no Xs associated with their name?

 

[karyn]

No here is what the rollup sheet look like now

A B C
1 Name Monday Start Monday End
2 Sue 800 1600
3 Joe #N/A #N/A
4 Tia 1100 1700

How can I return a value of OFF rather then #N/A?

Thanks

 

[T. Valko]

Ok, you misunderstood what I was asking...

The formulas will return #N/A for 1 of 2 reasons:

1. either Joe's name can't be found on the Monday sheet or,

2. Joe's name is on the Monday sheet but there are no Xs associated with
that name.

So, I want to write the error trap based on one of those conditions rather
than trapping the entire formula. This is why I suggested just letting the
#N/A errors happen then hiding them in my original reply.

 

[T. Valko]

What version of Excel are you using?

If you're using Excel 2007 the error trap is much easier!

 

[karyn]

Thank you for your help but is there a way to show this error as the employee
being OFF? Maybe populate another sheet and do a find and replace type of
formula?

 

[T. Valko]

Formula for column B:

=IF(ISNA(MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0)),"Off",INDEX(B$1:E$1,MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0)))

Formula for column C:

=IF(ISNA(MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0))),"Off",INDEX(B$1:E$1,MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0))))