VLOOKUP across sheets

[Drahmee]

Hi All,

This project is way above my abilities, so I'm asking for help.


Sheet 1 must contain the following data, in the following manner:

The employee's name in Column B, their Department in Column C, the day of the week in column D, their start time in Column E (on 24 hour clock), and their finish time in column F (on 24 hour clock). These five pieces of info must be listed in this manner, for a specific reason.

The sheets are set up in the following way:
Sheet 1 column titles (Columns B-F): Name, Department, Day of Week, Start Time, Finish Time
Sheet 2 and Sheet 3 column titles (Columns A-C): Sales, Shipping, Clerical
Sheet 2 and Sheet 3 row info (Rows 1-24): 00:00 - 01:00, 01:00 - 02:00, etc

If I want to use data from Sheet 1 to calculate staffing numbers for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 2 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

If I want to use data from Sheet 1 to display staff names for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 3 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.


Thanks in advance for the help!

Doug

 

[Coding4Fun]

Hi All,

Sheet 2 and Sheet 3 column titles (Columns A-C): Sales, Shipping, Clerical
Sheet 2 and Sheet 3 row info (Rows 1-24): 00:00 - 01:00, 01:00 - 02:00, etc

Question/Comment: Where in sheets 2 and 3 is the timestamp? (00:00 - 01:00, 01:00 - 02:00, etc), you mention it is on rows 1-24 but what column? Note this may note be valid as Rows 1-24 maybe 2-25?

Hi All,

If I want to use data from Sheet 1 to calculate staffing numbers for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 2 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

If I want to use data from Sheet 1 to display staff names for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 3 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

Question/Comment: You mention putting this in Sheets 2 and 3 in Cell A1 however you also indicated that there should be a header "Sales" in Column A which would be in conflict?

Please advise.

 

[Drahmee]

Question/Comment: Where in sheets 2 and 3 is the timestamp? (00:00 - 01:00, 01:00 - 02:00, etc), you mention it is on rows 1-24 but what column? Note this may note be valid as Rows 1-24 maybe 2-25?

Timestamp in column A



Question/Comment: You mention putting this in Sheets 2 and 3 in Cell A1 however you also indicated that there should be a header "Sales" in Column A which would be in conflict?

Header would be in row 1

Please advise.

Thanks!

 

[Coding4Fun]

Sheet 1 is fine as described. Sheet 2 you would want to add a time stamp column (Column A).

Columns A - D / Headers: A:TimeStamp, B:Sales, C:Shipping, D:Clerical

The following would be inserted into B2

=COUNTIFS(Sheet1!$C:$C,Sheet2!$B$1,Sheet1!$D:$D,"Monday",Sheet1!$E:$E, "<=" & TEXT(LEFT(Sheet2!$A2,5),"hh:mm"),Sheet1!$F:$F, ">=" & TEXT(RIGHT(Sheet2!$A2,5),"hh:mm"))

Copy this to C and D and then Change $B$1 to $C$1 and $D$1

Repeat the process on Sheet 3 (Not sure why 3 - Copy of 2 and 3)

Then on Sheet3 note that in the formula you need to change Sheet2 to Sheet3

You would also want to adjust "Monday" maybe to a cell that has the day in question. It also does not like hour 00:00 this is always off.

 

[Drahmee]

Hi,

Sheet 2 is to calculate how many staff at each time, and Sheet 3 is to list the names of all the people who fit each criteria, preferably separated by a comma.

Thanks!

 

[Drahmee]

Sheet 1 is the input sheet, Sheet 2 is the output sheet which displays the staffing levels as a number, and Sheet 3 is the output sheet which displays the names of those reflected in the numbers on Sheet 2.


Sheet 1:

[TABLE="width: 470"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD]Name[/TD]
[TD]Department[/TD]
[TD] Day[/TD]
[TD] Start time[/TD]
[TD] Finish Time[/TD]
[TD] Total hours[/TD]
[/TR]
[TR]
[TD] 2[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Monday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 3[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 4[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 5[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Thursday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 6[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Friday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 7[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Saturday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 8[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Sunday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 9[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Monday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Friday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Sunday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Monday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Friday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Saturday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 and 3:

[TABLE="width: 1349"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col span="3"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [TABLE="width: 1349"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col span="3"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[TD] H [/TD]
[TD] I [/TD]
[TD] J[/TD]
[TD] K[/TD]
[TD] L[/TD]
[TD] M[/TD]
[TD] N[/TD]
[TD] O[/TD]
[TD] P[/TD]
[TD] Q[/TD]
[TD] R[/TD]
[TD] S[/TD]
[TD] T[/TD]
[TD] U[/TD]
[TD] V[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] Timestamp[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD]Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]

[/TD]
[TD]Sunday[/TD]
[TD] Sunday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Wednesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Thursday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Friday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Saturday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]00:01-1:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]01:00-2:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD]02:00-3:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6 [/TD]
[TD]03:00-4:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7 [/TD]
[TD]04:00-5:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD]05:00-6:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9 [/TD]
[TD]06:00-7:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10 [/TD]
[TD]07:00-8:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11 [/TD]
[TD]08:00-9:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12 [/TD]
[TD]09:00-10:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13 [/TD]
[TD]10:00-11:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]14 [/TD]
[TD]11:00-12:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15 [/TD]
[TD]12:00-13:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]16 [/TD]
[TD]13:00-14:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]17 [/TD]
[TD]14:00-15:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]18 [/TD]
[TD]15:00-16:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]19 [/TD]
[TD]16:00-17:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]20 [/TD]
[TD]17:00-18:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]21 [/TD]
[TD]18:00-19:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]22 [/TD]
[TD]19:00-20:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]23 [/TD]
[TD]20:00-21:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]24 [/TD]
[TD]21:00-22:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]25 [/TD]
[TD]22:00-23:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]26 [/TD]
[TD]23:00-23:59[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[KolGuyXcel]

Sheet 1 is the input sheet, Sheet 2 is the output sheet which displays the staffing levels as a number, and Sheet 3 is the output sheet which displays the names of those reflected in the numbers on Sheet 2.


Sheet 1:

[TABLE="width: 470"]
<tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD]Name[/TD]
[TD]Department[/TD]
[TD] Day[/TD]
[TD] Start time[/TD]
[TD] Finish Time[/TD]
[TD] Total hours[/TD]
[/TR]
[TR]
[TD] 2[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Monday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 3[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 4[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 5[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Thursday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 6[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Friday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 7[/TD]
[TD]Doug[/TD]
[TD]Sales[/TD]
[TD]Saturday[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 8[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Sunday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] 9[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Monday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Frank[/TD]
[TD]Clerical[/TD]
[TD]Friday[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Sunday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Monday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Thursday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Friday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Angela[/TD]
[TD]Shipping[/TD]
[TD]Saturday[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 and 3:

[TABLE="width: 1349"]
<tbody>[TR]
[TD][/TD]
[TD] [TABLE="width: 1349"]
<tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[TD] H[/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD] K[/TD]
[TD] L[/TD]
[TD] M[/TD]
[TD] N[/TD]
[TD] O[/TD]
[TD] P[/TD]
[TD] Q[/TD]
[TD] R[/TD]
[TD] S[/TD]
[TD] T[/TD]
[TD] U[/TD]
[TD] V[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] Timestamp[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD]Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[TD] Sales[/TD]
[TD]Shipping[/TD]
[TD] Clerical[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Sunday[/TD]
[TD] Sunday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Wednesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Thursday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Friday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Saturday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]00:01-1:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]01:00-2:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]02:00-3:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]03:00-4:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]04:00-5:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]05:00-6:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]06:00-7:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]07:00-8:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]08:00-9:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]09:00-10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]10:00-11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]11:00-12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]12:00-13:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]13:00-14:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]14:00-15:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]15:00-16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]16:00-17:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]17:00-18:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]18:00-19:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]19:00-20:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]20:00-21:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]21:00-22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]22:00-23:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]23:00-23:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

We need to break the "total hours" data into hourly "categories" (The categories being "100-200", "200-300" and so on.)!

To do this we use the columns G through AE in Sheet1! Use 1 as column G heading, 2359 as column AE heading and the rest of the columns would have the headings 200, 300 until 2300 from columns H till column AD!

Then in the cells G2 through AD20 (not AE20) use this formula -->
=IF($D2=G$1,1,IF(AND($D2<G$1,$E2>=H$1),1,0))

Since here the original data ends in 20th row, we should paste the formula until that row!

The hourly breakup data is now available!

Then we need to TRANSPOSE this data!
Insert a new Sheet, Sheet4.
Select 20 columns (From A through T) and 31 rows.
Then in cell A1 type
=TRANSPOSE(Sheet1!A1:AE20) and press Ctrl+Shift+Enter --> the way to enter an array formula!

In Sheet2, from cells B3 till V26 paste the formula
=SUMIFS(Sheet4!$B7:$T7,Sheet4!$B$2:$T$2,Sheet2!B$1,Sheet4!$B$3:$T$3,Sheet2!B$2)

Sheet2 would be done!

I'm unsure of the Sheet3! If the count in any of the cells would be more than 1, are we to display ALL the names in the corresponding cell? Or is it mandatory that the cell values won't be more than 1?

 

[Drahmee]

<g$1,$e2>

I'm unsure of the Sheet3! If the count in any of the cells would be more than 1, are we to display ALL the names in the corresponding cell? Or is it mandatory that the cell values won't be more than 1?

The count in most cells will be above 1, and I'd like it to display all names, separated by a comma. </g$1,$e2>

 

[Drahmee]

The hourly breakup data doesn't seem to transfer correctly. No formulas work correctly, even the first one.

 

[KolGuyXcel]

The above formula (=IF($D2=G$1,1,IF(AND($D2) is working fine with me, when I use the given data! The cell values in the columns D and E i.e. the ones with the headings Start Time and Finish Time, are numbers in multiples of hundreds and not in any time format! And the formula is working fine! However, if those values are in time format i.e. something like 6:00 etc. then this formula won't work. Also, when I'm using the above given data, I'm using the 1st row for the headers (Name, Department, Day etc.) and not the column numbers (1,2,3 etc.).