way to track formula result changes??

[SuzBird]

Is there a way to track changes for formula results?

For example, if you enter "1" in A1, "2" in B1 and sum(A1+B1) in C1, i
there a way to track if C1 ever changes?

I have the gotten as far as the VBA code below, which only track
manual changes, but can't figure out how to do it for a formla result.
My formula result is based on a VLOOKUP.

I tried to get creative and do "paste as values" via a macros, but tha
doesn't work either.

This has been a major head-scratcher for me. Excel has gotta be abl
to do this somehow, right?

Any helpful hints much appreciated....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mynew As String
Dim myold As String
Dim myaddresss As String
If Target.Count > 5 Then Exit Sub
If Target.Column <> 8 And Target.Column <> 9 Then Exit Sub
Application.EnableEvents = False
myaddresss = ActiveCell.Address
mynew = Target.Value
Application.Undo
myold = Target.Value
Target.Value = mynew
If myold = Empty Then GoTo myskip
If myold = mynew Then GoTo myskip
Target.Offset(0, 2).Value = myold
myskip:
Range(myaddresss).Select
Application.EnableEvents = True
End Su

 

[Dave Peterson]

Maybe you could just use another sheet with the values in the same cells. Then
check to see if the value in the "mirrored" sheet still matches the value in the
real sheet.

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("c1:c10")

For Each myCell In myRng.Cells
If myCell.Value _
= Worksheets("mirrored").Range(myCell.Address).Value Then
'no change
Else
MsgBox "it changed at: " & myCell.Address
End If
Next myCell

End Sub

And maybe you could use the workbook_open event to load up the "mirrored" sheet.

(and hide mirrored to make it less vulnerable)