"ADD VALUE FROM DAY TO MONTH"

Posted by DALE MINKEL on February 13, 2002 3:57 AM

OKAY, HERE'S THE SCENARIO..
CELL $B$1 CONTAINS THE VALUE "50" (DAILY OVER/SHORT).
CELL $B$2 "NEEDS A FORMULA" OR SOMETHING THAT WILL TAKE TODAYS VALUE OF "50" AND THEN TOMORROW WHEN THE VALUE IN CELL $B$1 CHANGES TO, LET'S SAY, "14"..I NEED CELL $B$2 TO SAY "64". I GOT SOME PREVIOUS ADVICE ABOUT GOING TO TOOLS/OPTIONS/CALCULATION AND PUTTING A CHECK IN ITERATION AND SETTING THE MAX ITERATION TO 1, BUT THE PROBLEM IS I AM UNABLE TO JUST GET CELL $B$2 TO ADD FROM ONLY CELL $B$1..(IT ADDS ALL CHANGES FROM ALL OVER THE ENTIRE SHEET).CAN ANYBODY OFFER ANY HELP

Posted by Barrie Davidson on February 13, 2002 6:09 AM

Dale, you can't do this with a formula. You can, however, do this via VBA. Right click on your worksheet, select View Code, and paste this in the worksheet's code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim OldValue
If Target.Address &LT;&GT; "$B$1" Then Exit Sub
OldValue = Range("B2").Value
Range("B2").Value = OldValue + Target.Value
End Sub

Regards,
BarrieBarrie Davidson

Posted by DALE MINKEL on February 13, 2002 1:03 PM

? I ALSO TRIED THIS, BUT IT CONTINUED TO DO THE SAME THING...MAYBE I'M DOING SOMETHING WRONG..THANKS TO BARRIE FOR THE BELOW INFO..IT'S MORE THAN I KNEW WHEN I STARTED..DALE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim OldValue
If Target.Address &LT;&GT; "$B$1" Then Exit Sub
OldValue = Range("B2").Value
Range("B2").Value = OldValue + Target.Value
End Sub