Resetting cell value; after time/calculation has been made; Resolving circular ref.

  • Thread starter Thread starter Shocktech
  • Start date Start date
S

Shocktech

Im very new to excel 97 and have two questions/problems;

1. Can a cell be formatted to reset to a "0" value after a specifie
time or after it's value has been used/calculated?

2. I want to solve the following senerio;

cell a1 = an inserted value added to sum of a3; then resets to
after...3-5 seconds OR after A3l value has been calculated.

cell a2= an inserted value subtracted from sum of a3; then resets to
after...3-5 seconds OR after A3 value has been calculated.

cell a3=current balance. (A1/A2 use this value to add/sub from; hence
circular reference.

I'm trying to make a spreadsheet that keeps a running balance o
inventory with consideration of returned stock and stock out. I usuall
end up with a circular reference. Is there a way to do this or do i
differently?

Thanks for any help
 
I think I'd really try to use more cells to keep the old historical data. It
gives you a way of figuring out what happened.

But if you really want something automatic:

rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myMult As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a2")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then
MsgBox "Please enter a number!"
Exit Sub
End If

On Error GoTo errHandler:

With Target
If .Address(0, 0) = "A1" Then
myMult = 1
Else
myMult = -1
End If
Application.EnableEvents = False
If IsNumeric(Me.Range("a3").Value) Then
Me.Range("a3").Value = Me.Range("a3").Value + (myMult * .Value)
.Value = 0
Else
MsgBox "A3 isn't numeric!"
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Back to excel and test 'er out.
 
Back
Top