Cumulative total, here I go again...

Posted by Luis B on November 23, 2001 4:00 PM

I tried to ask this question before but I didn't make myself clear. On cell A1 the user will enter the date. On cell B2 there will be a number for that date, also input by the user (payroll cost for that day, say). Every day the date will be entered and the payroll cost will be entered on B2 (overwriting the previous day's amount). Question: how can I keep track of what the total is for cell B2 after x number of days?
Thanks,
Luis

Posted by Penfold on November 23, 2001 8:54 PM

Luis,

The trouble is you are OVERWRITING the data each time the user enters a new number. If you want a history (which is what a cumulative total really is) you need to keep a record of the numbers. Either edit your sheet so that the user enters a new date and number in a new cell each time or put a little macro in that copies the data to Sheet 2. For example, if the user needs to enter new data he should press the "Update" button which would copy the data to Sheet2, increment the date by one, and clear b2. Note that in Sheet2 you will need to Name Define a cell (eg "A2") Latest_Data - this will be your starting point for your history.

Now if you want cumulative total, averages or whatever, just put the appropriate formula in.

Regards,

Penfold

Sub Update_Data()
Dim c As Integer, r As Integer
c = Range("Latest_Data").Column
r = Range("Latest_Data").Row
Sheets("Sheet2").Cells(r, c).Value = Range("a1").Value
Sheets("Sheet2").Cells(r, c + 1).Value = Range("b2").Value
ActiveWorkbook.Names("Latest_Data").Delete
ActiveWorkbook.Names.Add Name:="Latest_Data", RefersTo:=Sheets("Sheet2").Cells(r + 1, c)
Range("a1").Value = Range("a1").Value + 1
Range("b2").ClearContents
End Sub