continuously adding numbers day to day

  • Thread starter Thread starter KMF
  • Start date Start date
K

KMF

If I construct an excel invoice and each day I put in a
different number of items sold on the invoice is there a
way that a cell on a separate excel sheet can add these
changing daily numbers automatically so at the end of a
month I have the total added on the other sheet or after
I create the daily invoice do I have to then go to the
other sheet and add up all the separate numbers?
Thanks for anyones help.
 
KMF,

You can use the worksheet's change event. Copy one of the code examples
below, right click on the sheet tab, select "View code", then paste the
code into the window that appears. You'll need to change the addresses
and sheet names.

These examples will keep a record of any changes to cell A1 on the sheet
where the code is used. Both will keep their records on Sheet2.

Note that the first version keeps a sum, while the second keeps a record
that can later be summed using a formula.

Note that if you ever need to change a value - say you entered 10 and meant
to enter 11 - then you need to undo your change by entering the negative
value of your mistake, or -10 in this example. If you are using the second
version, you can also edit "Sheet2".

HTH,
Bernie
MS Excel MVP

'Use this version to simply keep a running sum
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Worksheets("Sheet2").Range("A1").Value = _
Worksheets("Sheet2").Range("A1").Value + Target.Value
End If
End Sub

'Use this verison to store a record
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Worksheets("Sheet2").Range("A65536").End(xlUp)(2).Value = Target.Value
End If
End Sub
 
Thanks, is it possible to use the formula below in more
than one cell? I would like to add several items in
different cells on one worksheet and have them added
(accumulated) in designated cells in another worksheet.
 
Kevin,

To do multiple cells, simply repeat the basic code block, with the different
address 'pairs' that you want to use: see the example below. Target always
refers to the cell being changed, so adding Target.Value in each set is
fine. Give it a try, and post back if you need more help.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Worksheets("Sheet2").Range("A1").Value = _
Worksheets("Sheet2").Range("A1").Value + Target.Value
End If
If Target.Address = "$B$10" Then
Worksheets("Sheet2").Range("B2").Value = _
Worksheets("Sheet2").Range("B2").Value + Target.Value
End If
If Target.Address = "$D$20" Then
Worksheets("Sheet2").Range("C3").Value = _
Worksheets("Sheet2").Range("C3").Value + Target.Value
End If
End Sub
 
Back
Top