|
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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 |
Employee Scheduling Worksheet
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" wrote: 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 |
Employee Scheduling Worksheet
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. -- Biff Microsoft Excel MVP "karyn" wrote in message ... 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" wrote: 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 |
Employee Scheduling Worksheet
What version of Excel are you using?
If you're using Excel 2007 the error trap is much easier! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "karyn" wrote in message ... 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" wrote: 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 |
Employee Scheduling Worksheet
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" wrote: 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. -- Biff Microsoft Excel MVP "karyn" wrote in message ... 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" wrote: 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 |
Employee Scheduling Worksheet
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)))) -- Biff Microsoft Excel MVP "karyn" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "karyn" wrote in message ... 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" wrote: 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 |
| All times are GMT +1. The time now is 07:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com