help with formula

[brit]

Pretty new to all this so I'll explain my problem...

I need to add a value to a cell a1 and for this to be copied to cell b1. However I then want to return the value in cell a1 to 0 or any other value whilst protecting the value in cell b1 and adding the new value of a1 to that protected value.
Can this be done?
If so what formula do I use.
Thanx in advance.

 

[eliW]

On 2002-08-25 01:54, brit wrote:
Pretty new to all this so I'll explain my problem...

I need to add a value to a cell a1 and for this to be copied to cell b1. However I then want to return the value in cell a1 to 0 or any other value whilst protecting the value in cell b1 and adding the new value of a1 to that protected value.
Can this be done?
If so what formula do I use.
Thanx in advance.

Hi brit and welcome to the board!

Formula is no good as it will turn to be circular.

you can:
1) enter value in A1
2) copy it and Paste Special to B1 choosing Add option
3) deleting the value in A1
4) goto (1)

You may automate this procedure by Macro

Eli

 

[brit]

Thanx for the reply, I did in fact get the circular return you mention.

 

[Aladin Akyurek]

On 2002-08-25 01:54, brit wrote:
Pretty new to all this so I'll explain my problem...

I need to add a value to a cell a1 and for this to be copied to cell b1. However I then want to return the value in cell a1 to 0 or any other value whilst protecting the value in cell b1 and adding the new value of a1 to that protected value.
Can this be done?
If so what formula do I use.
Thanx in advance.

You might want to experiment with:

=RECALL(A1)+A1

to be entered in B1.

This function is part of the morefunc add-in, available from:

http://longre.free.fr/english/index.html

 

[brit]

I'll give that a try, the first answer didn't do what I was attempting, it kept zeroing the answer when cell a1 was deleted.

 

[Ekim]

Right click on the sheet tab and choose View Code. Copy and paste the following into a sheet module (not the ThisWorkbook module) - if you are doing the number accumulation in say sheet 1 then put the code in the sheet 1 module.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ("$B$1") Then
Range("$A$1").Value = Range("$A$1").Value + Target.Value
End If
End Sub


Now save the file.

Put your first number in cell B1 - it will also appear in cell A1. The next number that you enter in B1 will be automatically added to the first number in A1 etc.

Kind regards,

Mike

 

[brit]

Ekim, that is exactly what I wanted, thanx.