clear cell automatically

[eyoung99]

i have created a circular reference on purpose, with the intent to enter data in one column that is automatically added and stored in another column. for example, i have cell d3 set up as a formula =b3+d3. originally, d3 is set to 0. when i enter data into b3 it updates d3 by the value entered in b3.

if it were just the one cell there would be no problem, but i am doing this for d3, d4, and d5 respectively. the problem occurs as follows. when i enter my value for b3, d3 is updated correctly. if i enter a value in b4 to update d4 and have not cleared out the value in b3, the value for d3 is again increased by the value in b3.

i can manually clear out each value after i have entered them, but i was wondering if there is a way to clear contents of the cell once hitting the enter button or if there is a better way to avoid this problem.

 

[VoG]

I think that you would be better off with code. Remove the formulas, right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3:B5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, 2).Value = .Offset(, 2).Value + .Value
    .ClearContents
End With
Application.EnableEvents = True
End Sub

Now observe the effect of entering values in B3, B4 and B5.

 

[lenze]

Hi and welcome to the board!!!
Use a macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Cells(Target.Row, "C") = Cells(Target.Row, "C") + Target
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End Sub

Place in the WorkSheet module. RightClick the sheet tab and choose "View Code"

lenze

 

[BAlGaInTl]

Are you looking for something like this?

Book2
	B	C	D	E
3	1		1
4	2		3
5	3		6
6	4		10
7	5		15
Sheet2

 

[eyoung99]

No to BAlGaInTl. Probably the others are correct, but I have never used code before. Thank you; I'll give it a shot.

 

[BAlGaInTl]

No to BAlGaInTl. Probably the others are correct, but I have never used code before. Thank you; I'll give it a shot.

Sorry about that...

I misread your requirements.

oops.