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

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

"Colin Fisher" wrote:

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

This is a bad idea!

What happens when the user makes a mistake?

There's no way to undo it.

--
Biff
Microsoft Excel MVP


"Colin Fisher" <Colin wrote in message
...
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

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
wrote:

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

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
"Mike H" wrote:

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

"Colin Fisher" wrote:

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

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" wrote:

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
wrote:

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

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

On Wed, 21 Jan 2009 11:39:01 -0800, Colin Fisher
wrote:

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" wrote:

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
wrote:

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