I'm trying to create a summation sheet of the overtime hours worked by
employees, but don't want to track the actual hours / days they worked on
this sheet, just the total hours they've worked over the year. I'd like to be
able to have cells for each day of the week in the columns, and the person
name in rows. For each day of the week, you could then enter any overtime
worked. I've come across a macro that accomplishes what I need should I be
entering data for just a single cell (titled the OnEntry macro); however, I
need to create a sum for the week and have that total added to the previous
week.

Example problem:

John = 22 Hours of overtime in the year
Dave = 12 Hours of overtime in the year
Amy = 36 Hours of overtime in the year

This week John worked 12 hours of overtime on Monday, and 12 hours on
Friday, while Dave and Amy worked none.
I would like to be able to simply enter 12 in the cell corresponding
with John & Monday as well as John & Friday, get the total for those two
days, and add them to the 22 hours John has already worked in the year. Then,
have that number retained (now 46 hours instead of 22) while I clear out the
hours and move onto the next week.

Any help would be appreciated.

CVinje

As ALWAYS, post YOUR code for comment and suggestions. Also, give layout and
before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CVinje" wrote in message
...
I'm trying to create a summation sheet of the overtime hours worked by
employees, but don't want to track the actual hours / days they worked on
this sheet, just the total hours they've worked over the year. I'd like to
be
able to have cells for each day of the week in the columns, and the person
name in rows. For each day of the week, you could then enter any overtime
worked. I've come across a macro that accomplishes what I need should I be
entering data for just a single cell (titled the OnEntry macro); however,
I
need to create a sum for the week and have that total added to the
previous
week.

Example problem:

John = 22 Hours of overtime in the year
Dave = 12 Hours of overtime in the year
Amy = 36 Hours of overtime in the year

This week John worked 12 hours of overtime on Monday, and 12 hours on
Friday, while Dave and Amy worked none.
I would like to be able to simply enter 12 in the cell corresponding
with John & Monday as well as John & Friday, get the total for those two
days, and add them to the 22 hours John has already worked in the year.
Then,
have that number retained (now 46 hours instead of 22) while I clear out
the
hours and move onto the next week.

Any help would be appreciated.

CVinje

There is no before and after example - I'm not sure what you're requesting by
that?

==================================================

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total
Year Total
John 12 0 0 0 12 0 0
24 46
Dave 0 0 0 0 0 0 0
0 12
Amy 0 0 0 0 0 0 0
0 36

1)The year total column would automatically add the hours from the week.
(John had 22 hours before the input of the overtime)
2)The Year total retains it's value once the overtime for the week is removed.
3)The week total column would zero once the hours worked in the week are
removed.

================================================== =

Code example for retaining cumulative value in a single cell (not what I
need):

Code from Microsoft.com: http://support.microsoft.com/default.aspx/kb/213445

' The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub

<<CORRECTED FORMATTING ATTEMPT

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total Year Total
John 12 0 0 0 12 0 0 24
46
Dave 0 0 0 0 0 0 0 0
12
Amy 0 0 0 0 0 0 0 0
36

1)The year total column would automatically add the hours from the week.
2)The Year total retains it's value once the overtime for the week is removed.
3)The week total column would zero once the hours worked in the week are
removed.

Sub keepyrtotal()
For i = 2 To Cells(Rows.Count, "j").End(xlUp).Row
Cells(i, "j") = Cells(i, "J") + Cells(i, "I")
'to clear mon:Sun
Cells(i, "b").Resize(, 7).ClearContents
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CVinje" wrote in message
...
There is no before and after example - I'm not sure what you're requesting
by
that?

==================================================

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total
Year Total
John 12 0 0 0 12 0 0
24 46
Dave 0 0 0 0 0 0 0
0 12
Amy 0 0 0 0 0 0 0
0 36

1)The year total column would automatically add the hours from the week.
(John had 22 hours before the input of the overtime)
2)The Year total retains it's value once the overtime for the week is
removed.
3)The week total column would zero once the hours worked in the week are
removed.

================================================== =

Code example for retaining cumulative value in a single cell (not what I
need):

Code from Microsoft.com:
http://support.microsoft.com/default.aspx/kb/213445

' The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub

Thank you sir for the help - with some modification, this works perfectly!!

"Don Guillett" wrote:

Sub keepyrtotal()
For i = 2 To Cells(Rows.Count, "j").End(xlUp).Row
Cells(i, "j") = Cells(i, "J") + Cells(i, "I")
'to clear mon:Sun
Cells(i, "b").Resize(, 7).ClearContents
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CVinje" wrote in message
...
There is no before and after example - I'm not sure what you're requesting
by
that?

==================================================

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total
Year Total
John 12 0 0 0 12 0 0
24 46
Dave 0 0 0 0 0 0 0
0 12
Amy 0 0 0 0 0 0 0
0 36

1)The year total column would automatically add the hours from the week.
(John had 22 hours before the input of the overtime)
2)The Year total retains it's value once the overtime for the week is
removed.
3)The week total column would zero once the hours worked in the week are
removed.

================================================== =

Code example for retaining cumulative value in a single cell (not what I
need):

Code from Microsoft.com:
http://support.microsoft.com/default.aspx/kb/213445

' The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub

Worked properly, as tested. Post your modifications.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CVinje" wrote in message
...
Thank you sir for the help - with some modification, this works
perfectly!!

"Don Guillett" wrote:

Sub keepyrtotal()
For i = 2 To Cells(Rows.Count, "j").End(xlUp).Row
Cells(i, "j") = Cells(i, "J") + Cells(i, "I")
'to clear mon:Sun
Cells(i, "b").Resize(, 7).ClearContents
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CVinje" wrote in message
...
There is no before and after example - I'm not sure what you're
requesting
by
that?

==================================================

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total
Year Total
John 12 0 0 0 12 0 0
24 46
Dave 0 0 0 0 0 0 0
0 12
Amy 0 0 0 0 0 0 0
0 36

1)The year total column would automatically add the hours from the
week.
(John had 22 hours before the input of the overtime)
2)The Year total retains it's value once the overtime for the week is
removed.
3)The week total column would zero once the hours worked in the week
are
removed.

================================================== =

Code example for retaining cumulative value in a single cell (not what
I
need):

Code from Microsoft.com:
http://support.microsoft.com/default.aspx/kb/213445

' The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub