Recalculating schedule

[Andy Kremp]

I'm creating a monthly schedule where different daily shifts ar
different lengths (8, 10, and 12 hours) and at different times. I nee
to calculate the total number of hours for each employee worked on tha
particular 4 week schedule.

I've created a 2-worksheet schedule where the first sheet is strictly
master schedule with names and slots. The second page is a mirror o
sorts, but it for the most part blank. However, when the employee o
interest is selected from a drop-down box, a number (=shift length
appears on this personal schedule in the same location as their name o
the regular schedule. (This is done with a simple "if [correspondin
cell on master sheet] = employee of interest, this cell = whatever th
shift length is (which varies by row)". That way the only things see
on the personal schedule are the shift lengths for that particula
employee on that particular day. The whole block can simply be summe
in a range to find out the total number of hours for that particula
employee.

Here's the tricky part: I'd like to keep a list of all employees an
total hours. Every time I select a particular employee, the workshee
calculates their total hours. However, I can't figure out how t
either get Excel to automatically cycle through each employee to ge
individual totals or to simultaneously calculate the totals.

If anyone has a more elegant way of performing the first part, tha
might be more useful and make it easier to get the second par
(totals).

Any ideas

 

[Guest]

And
Have you thought about using a pivot table with name in group
or
you could try something like this if i understand what you wan
enter change function in code for sheet2 this calls macro mymacro on change in this shee
Private Sub WORKSHEET_CHANGE(ByVal TARGET As Excel.Range

mymacr
end su
then write macro like mymacro to copy data to sheet3 every time select new employee data will copy to new line on sheet
Sub mymacro(
Range("a1:b1").Cop
Sheets("Sheet3").Selec
Range("A65536").Selec
Selection.End(xlUp).Selec
If ActiveCell = " " The
Els
ActiveCell.Offset(1, 0).Selec
End I
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Application.CutCopyMode = Fals

End Su

obviously your data to copy will not be a1:b1 but should beable to alter to suit
I have reread ny reply and don't know if makes sense maybe you should look at site
www.mcgimpsey.com/exce
o
www.mvps.org/dmritchie/exce
both have helped me in pas

good luck
Tin