total formula

[shawn]

example, cell b4 contains 3. How can I get cell c4 to display b4 contents and
only add to total when b4 changes. Total being displayed in c4.

entered 3 in b4 for units ordered yesterday
entered 2 in b4 for units ordered today

want total to equal 5 but only want to enter orders daily, if this makes
sense.

 

[Gary''s Student]

You need an accumulator. Enter the following event macro in the worksheet
code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("C4").Value = Range("C4").Value + Range("B4").Value
Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Luke M]

You could use this worksheet_change macro. Macro checks a time stamp (placed
in D4, change if you need to) and if total has not been updated today, then
updates total in C4.

To place macro, right click on sheet tab, view code, paste this in:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B4")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

'Check time stamp
'if data already entered today, do nothing
If Range("D4") = Date Then Exit Sub

'Add values
Range("C4") = Range("C4").Value + Range("B4").Value
'Update time stamp
Range("D4").Value = Date

End Sub
'=============

 

[Gord Dibben]

You have been shown by others how to use event code to create an
accumulator.

Be aware that this type of operation allows no paper trail for error
checking when incorrect data is entered.

I say "when" not "if"


Gord Dibben MS Excel MVP