Year To Date Figure
Posted by Gayle Munro on May 18, 2001 8:43 AM
Example of what I am trying to do.
Columns
A B
Current Year to Date
Row 1 100 200
The Current number in Column A will change every month.
I would like the new number to be added to the Year to
Date figure in Column B. Ex: If the number in Column A
is changed to 300, then the 300 would be added to
the existing number of 200 in Column B and Column B would
now be 500. Any help would be appreciated. Thanks.
Posted by Dave Hawley on May 18, 2001 9:17 AM
Hi Gayle
You could create a Circular Reference to do this but this can cause side effects. Try this instead. Right click on the Sheet name tab and select "View Code", copy and paste in this Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
Dim rWatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
Set rWatchRange = Range("A2:A100")
If Not Intersect(Target, rWatchRange) Is Nothing Then
If IsNumeric(Target) And IsNumeric(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Target + Target.Offset(0, 1)
End If
End If
Set rWatchRange = Nothing
End Sub
You can change the range A2:A100 to suit.
Dave
OzGrid Business Applications
Posted by Roy Hernandez on May 19, 2001 1:17 PM
Posted by ROY HERNANDEZ on May 19, 2001 1:21 PM
DAVE GREAT TIP BUT HOW CAN I APPLY SAME PRINCIPLE FOR SEVERAL COLUMNS. FOR EXAMPLE...
A B C D E F
MTD YTD MTD YTD MTD YTD
Posted by Dave Hawley on May 20, 2001 8:01 AM
A B C D E F
Hi Roy
Use this one instead.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
Dim rWatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
Set rWatchRange = Range("A:A,C:C,E:E,G:G")
If Not Intersect(Target, rWatchRange) Is Nothing Then
If IsNumeric(Target) And IsNumeric(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Target + Target.Offset(0, 1)
End If
End If
Set rWatchRange = Nothing
End Sub
Dave
OzGrid Business Applications
