displaying running total of value entered in same cell?

[Colin Fisher]

I want to allow the user to type an Integer value into a worksheet cell. The
cell value is added to the previous cell entries from that same cell and
displayed in a linked cell on a separate worksheet.

If possible, all values for previous weeks entries on the worksheet can be
retrieved and displayed without the need to store each spreadsheet as a
separate file.

Any help on this would be grealty appreciated

 

[Mike H]

Hi,

Right click your sheet tab, view code and psate this in. It takes the calue
from A1 and accumullates those values in sheet2 A2. Accepts integer values
only

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" And IsNumeric(Target) And _
Target.Value - Int(Target.Value) = 0 Then
Sheets("Sheet2").Range("A2").Value _
= Sheets("Sheet2").Range("A2").Value + Target.Value
End If
End Sub

Mike

 

[T. Valko]

This is a bad idea!

What happens when the user makes a mistake?

There's no way to undo it.

 

[Gord Dibben]

An accumulator cell idea is risky idea. Mistakes are difficult to find and
correct.

Having said that, here is a method you can try.

Add this event code to the sheet with the entry cell at A1

Add a new sheet named Retriever

You can hide that sheet if you wish. It is just used to collect the entries
made in A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'accumulator/summer
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
Sheets("Retriever").Cells(Rows.Count, "A").End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

In whatever cell on the "separate worksheet" you want the accumulated value
enter =SUM(Retriever!A2:A1000)

If user makes a mistake you at least have a list of all entries to peruse.

Note: to correct a mistake, you will have enter a negative number to
subtract the mistake from the SUM formula.



Gord Dibben MS Excel MVP

On Sun, 18 Jan 2009 06:22:07 -0800, Colin Fisher <Colin

 

[Colin Fisher]

Hi Mike

Thank you for the response. I will give the coding a try. Pehaps if I can be
more specific about the problem, you might know a better way around it.

User is entering several integer values on a table. Each value is to be
added to a sum on another table. Perhaps a method of verification could be
introduced to cut down on the chance of errors in data entry. It would be
helpful if the data for each weeks entries could be retained and displayed by
entering the date for the beginning of that week. Unfortunately the number
of weeks is unknown.

Thanks for the time and effort you have provided me with.

Colin

 

[Colin Fisher]

Gord

Many thanks for the time you put into coming up with a possible solution. I
will be giving it a try. I do need for the user to be able to view
historical data that has been entered. Your method should facilitate this.

Colin

 

[Gord Dibben]

Thanks for the feedback.

With a slight change you can also get date and time of each entry.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'accumulator/summer
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
With Sheets("Retriever").Cells(Rows.Count, "A").End(xlUp) _
.Offset(1, 0)
.Value = Target.Value
.Offset(0, 1).Value = Now
End With
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord