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

Here's an idea to get you started.

...........A..........B.........C..........D...... ....E
1..................800......830.......900.......93 0
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.

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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

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?

"T. Valko" wrote:

Here's an idea to get you started.

...........A..........B.........C..........D...... ....E
1..................800......830.......900.......93 0
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.

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"karyn" wrote:

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?

"T. Valko" wrote:

Here's an idea to get you started.

...........A..........B.........C..........D...... ....E
1..................800......830.......900.......93 0
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.

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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

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
1 Start End
2 Sue 800 900
3 Joe 830 930
4 Tia #N/A #N/A

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?




"Shane Devenshire" wrote:

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"karyn" wrote:

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?

"T. Valko" wrote:

Here's an idea to get you started.

...........A..........B.........C..........D...... ....E
1..................800......830.......900.......93 0
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.

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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

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?

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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
1 Start End
2 Sue 800 900
3 Joe 830 930
4 Tia #N/A #N/A

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?




"Shane Devenshire" wrote:

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"karyn" wrote:

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?

"T. Valko" wrote:

Here's an idea to get you started.

...........A..........B.........C..........D...... ....E
1..................800......830.......900.......93 0
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.

--
Biff
Microsoft Excel MVP


"karyn" wrote in message
...
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