How do I create a cumlative count in excel?

[ciscoric]

We have a production summary report that needs to keep a daily count of what was produced on each job.
The excel sheet has a column with the number of pieces produced today, and a column with the total number of pieces produced. We want to be able to enter the number produced today in the number of pieces produced today cell, and have that update the total pieces cell with a running sum.
Example:

Monday's entry would be:
A B C
1 Job# Total Daily Total Count
2 12163 1,000 1,000

Tuesday's entry would be:
A B C
1 Job# Total Daily Total Count
2 12163 2,000 3,000

Would would like to enter the data into the same cell every day and automatically accumulate the running total.

 

[SkipVought]

Hi,

This can probably be accomplished with native spreadsheet functionality.

Where do you get the totals for the day?

Skip,
[sub]
[red][/red]
[/sub]

 

[ciscoric]

Thank you for the reply SkipVought,

The totals for the day would be manually entered each day by the supervisor at the end of the shift.

 

[Bong]

How about another column with an "update flag"? Then a button could read the flag; if it's "yes", then calc the total and set the flag to "no". You could either have the user set the flag to "yes" or have it automatically change on open.

_________________
Bob Rashkin

 

[SkipVought]

Then add the data to your table along with the date.

The results can be a simple SUMPRODUCT as posted in the Office Forum.

Skip,
[sub]
[red][/red]
[/sub]

 

[feipezi]

This is one way of doing it. I tested it and it worked.
Let me tell you what I did.
1) open a new Workbook;
2) at the Sheet1, put in the following (not A,B,C):

A B C
jobnum daily total
12163

3) put the following code in Sheet1 codemodule (Viewcode)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("b2")) Is Nothing Then
Sheets("sheet2").Activate
ActiveCell.Value = Range("b2")
ActiveCell.Offset(1, 0).Activate
Call test
End If
End Sub
Sub test()
Sheets("sheet1").Range("c2") = Application.Sum(Sheets("sheet2").UsedRange)
Sheets("sheet1").Activate
End Sub

Maybe the spreadsheet functions can handle this sort of situation but I am not sure.

If you keep keying in numbers at $B$2 of Sheet1, every day, it will work for about 179 years if you don't make any changes in Sheet2 (if it's 2003 version of Excel, it will last about 2,849 years)

Hope this helps.

 

[ciscoric]

We have a winner!

Thank you feipezi, this code works perfectly. It is exactly what I have been searching for for years. I will put this into action imediatly.

Ric Johnson