I am using Excel 2000. I want my cell to remember the number in it and add
an additional number each time I need to retotal.

Jab

Sounds like you want that cell to be an accumlator cell.

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in ToolsOptionsCalculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and EditEnter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP


On Sun, 27 Feb 2005 15:17:04 -0800, jab wrote:

I am using Excel 2000. I want my cell to remember the number in it and add
an additional number each time I need to retotal.

Thanks for youir help, I wantn't try that method its to risky.

I my form, I have three columns I am work with. I am making a spread sheet
so I can keep up with how must of a items is used for week to week. I want to
be able to enter a number in one column, say 50 that will be the fix amount.
I then want to have another colum to enter the items used that week, and them
another column to keep a track of how many been used for the project. Could
you help me, Thanks

"jab" wrote:

I am using Excel 2000. I want my cell to remember the number in it and add
an additional number each time I need to retotal.

Gord,

Could it perhaps be done in a safer way by putting the the accumulated
value of the cell in its cell comment each time a new value is entered in
the cell; or the other way round, the newly entered value in the comment and
the accumulated value in the cell.
This way no extra column is needed.
To my regret it would not be easy - or even not possible - for me to write
the code, but for you it's peanuts, I'm sure of that.

Could this be a solution that Jab can live with?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Jab

Sounds like you want that cell to be an accumlator cell.

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in ToolsOptionsCalculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and EditEnter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP


On Sun, 27 Feb 2005 15:17:04 -0800, jab
wrote:

I am using Excel 2000. I want my cell to remember the number in it and
add
an additional number each time I need to retotal.

Jab

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

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

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A2 as the input cell, any new number entered will be automatically
placed into Column B(starting at B2)at the next available empty row.

I would enter in C2 =Sum(B:B) or =Sum(B2:B500). Whatever you think you need
to gather all future values in Column B.

Now you have a "paper trail" and a Totalizer cell(C2)

Note: if a mistake is made in last entered number in A2 , you will have to
delete the contents of the last cell in Column B then re-enter in A2.


Gord

On Sun, 27 Feb 2005 19:01:02 -0800, jab wrote:

Thanks for youir help, I wantn't try that method its to risky.

I my form, I have three columns I am work with. I am making a spread sheet
so I can keep up with how must of a items is used for week to week. I want to
be able to enter a number in one column, say 50 that will be the fix amount.
I then want to have another colum to enter the items used that week, and them
another column to keep a track of how many been used for the project. Could
you help me, Thanks

"jab" wrote:

I am using Excel 2000. I want my cell to remember the number in it and add
an additional number each time I need to retotal.

Jack

I'm not sure how to do that or whether or not it would be safer.

Sounds like it would still give no paper trail and be difficult to check for
errors.

Perhaps one of the "real" programmers could devise a method?


Gord

On Mon, 28 Feb 2005 09:50:08 +0100, "Jack Sons" wrote:

Gord,

Could it perhaps be done in a safer way by putting the the accumulated
value of the cell in its cell comment each time a new value is entered in
the cell; or the other way round, the newly entered value in the comment and
the accumulated value in the cell.
This way no extra column is needed.
To my regret it would not be easy - or even not possible - for me to write
the code, but for you it's peanuts, I'm sure of that.

Could this be a solution that Jab can live with?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
Jab

Sounds like you want that cell to be an accumlator cell.

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in ToolsOptionsCalculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and EditEnter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP


On Sun, 27 Feb 2005 15:17:04 -0800, jab
wrote:

I am using Excel 2000. I want my cell to remember the number in it and
add
an additional number each time I need to retotal.

Jab and Gord,

I tried a lot and with very much help from Tim Williams I got the code
below. Each time a new number is entered in a cell that new number is added
to the content of the comment of that cell. So no need for extra columns.
The last entry remains in the cell so it is "remembered" and the accumulated
sum of all entries in that cell is in its comment. Is that what you where
looking for Jab?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cmt As Comment

If Target.Cells.Count 1 Then Exit Sub

On Error Resume Next
Set cmt = Target.Comment
On Error GoTo 0

If cmt Is Nothing Then
Set cmt = Target.AddComment(Text:="0")
End If

If cmt.Text < "" Then
cmt.Text CStr(Target.Value + CDbl(cmt.Text))
End If

End Sub



"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Jab

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

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

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A2 as the input cell, any new number entered will be automatically
placed into Column B(starting at B2)at the next available empty row.

I would enter in C2 =Sum(B:B) or =Sum(B2:B500). Whatever you think you
need
to gather all future values in Column B.

Now you have a "paper trail" and a Totalizer cell(C2)

Note: if a mistake is made in last entered number in A2 , you will have to
delete the contents of the last cell in Column B then re-enter in A2.


Gord

On Sun, 27 Feb 2005 19:01:02 -0800, jab
wrote:

Thanks for youir help, I wantn't try that method its to risky.

I my form, I have three columns I am work with. I am making a spread
sheet
so I can keep up with how must of a items is used for week to week. I want
to
be able to enter a number in one column, say 50 that will be the fix
amount.
I then want to have another colum to enter the items used that week, and
them
another column to keep a track of how many been used for the project.
Could
you help me, Thanks

"jab" wrote:

I am using Excel 2000. I want my cell to remember the number in it and
add
an additional number each time I need to retotal.

Jack

Your code will just keep adding the current entered value to the value in the
comment box, but still has no "paper trail" or a way of correcting an
incorrect entry other than entering a negative of the incorrect entry and
re-entering the correct entry.

I prefer the listing of the numbers in a separate column so's I can check all
the inputs.


Gord

On Fri, 4 Mar 2005 10:05:58 +0100, "Jack Sons" wrote:

Jab and Gord,

I tried a lot and with very much help from Tim Williams I got the code
below. Each time a new number is entered in a cell that new number is added
to the content of the comment of that cell. So no need for extra columns.
The last entry remains in the cell so it is "remembered" and the accumulated
sum of all entries in that cell is in its comment. Is that what you where
looking for Jab?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cmt As Comment

If Target.Cells.Count 1 Then Exit Sub

On Error Resume Next
Set cmt = Target.Comment
On Error GoTo 0

If cmt Is Nothing Then
Set cmt = Target.AddComment(Text:="0")
End If

If cmt.Text < "" Then
cmt.Text CStr(Target.Value + CDbl(cmt.Text))
End If

End Sub



"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
Jab

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

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

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A2 as the input cell, any new number entered will be automatically
placed into Column B(starting at B2)at the next available empty row.

I would enter in C2 =Sum(B:B) or =Sum(B2:B500). Whatever you think you
need
to gather all future values in Column B.

Now you have a "paper trail" and a Totalizer cell(C2)

Note: if a mistake is made in last entered number in A2 , you will have to
delete the contents of the last cell in Column B then re-enter in A2.


Gord

On Sun, 27 Feb 2005 19:01:02 -0800, jab
wrote:

Thanks for youir help, I wantn't try that method its to risky.

I my form, I have three columns I am work with. I am making a spread
sheet
so I can keep up with how must of a items is used for week to week. I want
to
be able to enter a number in one column, say 50 that will be the fix
amount.
I then want to have another colum to enter the items used that week, and
them
another column to keep a track of how many been used for the project.
Could
you help me, Thanks

"jab" wrote:

I am using Excel 2000. I want my cell to remember the number in it and
add
an additional number each time I need to retotal.