none
How to make an accumulator cell and reset the user cell value RRS feed

  • 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

    Wednesday, December 28, 2016 3:34 AM

All replies

  • 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 MSDNFSF@microsoft.com.



    Wednesday, December 28, 2016 6:26 AM
    Moderator