How to make an accumulator cell and reset the user cell value

Question

Hello,

So basically what I want to do is create a function which will reset a cell's value to 0 if a user types a string in another cell.

 BUT, will continue to accumulate value AFTER the reset.

I was thinking of using an IF statement - something like =IF($E4="yes",0,'Enter Sheet'!$D$5), but I have no idea how to let the cell continue to accumulate value AFTER the reset. I've also heard something about the use of VBA codes possibly?

On another note, I also do not know how to make a cell reset itself to a blank cell after a user types in a string/value, any idea how to do it?

Cheers,

Jake

Answer 1

Hi,

>>how to let the cell continue to accumulate value AFTER the reset

Using UDF, I think it is impossible because we are unable to get the old in the cell which using the UDF.

To accumulate value, we could handle Worksheet_Change event. Without the target condition, it maybe endless loop.

E.g.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + Target.Value

End If

End Sub

>>On another note, I also do not know how to make a cell reset itself to a blank cell after a user types in a string/value, any idea how to do it?

If you want to restrict the value of cells, I would suggest you Apply data validation to cells.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].