Selected Answer
Pitty
It is possible to timestamp for a change in formula value by detecting a change in the data sheet (using the Worksheet_Change event) and tracing dependent cells then timestamping for those rows. This method works for all cells whose formula includes the changed cell and doesn't need you to map cells from one sheet to another.
In the attached demo file, I've done that so you can:
- Change a value in Sheet1 A2:B8 and the timestamps/ causes* will be recorded.
- Change a value in Sheet2 D2:D12 then activate Sheet1 to see the timestamps for formula value changes/ causes.
* I added a cell in column E which shows what caused (only) the last two changes of timestamp.
How does it work? Sheet1 has this event macro, based on Don's tutorial Automatically Timestamp Entries in Excel but with settings /changes in bold below:
Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
' TeachExcel.com
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("A2:B8")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Column for the date/time
Set myDateTimeRange = Range("C" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("D" & Target.Row)
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'### State last two causes of timestamps
With myUpdatedRange.Offset(0, 1)
.Value = Cells(1, Target.Column).Value & " changed; " & .Text
If InStrRev(.Text, ";") > InStr(.Text, ";") Then
.Value = Left(.Text, InStr(InStr(.Text, ";") + 2, .Text, ";"))
End If
End With
'Turn events back on
Application.EnableEvents = True
End Sub
If you change something in A2:B8 of Sheet1, a timestamp will appear in C or D and column E might get a cause like "Cost formula changed" (but it will show only the last two causes).
This doesn't work for formulas (dependent on value changes on Sheet2) so that sheet has the following Worksheet_Change event (working for just the first range in bold and with comments for guidance):
Public Evnt As String
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelRange As Range, ArrowNum As Long
' expect arrow navigation to fail
On Error GoTo Done
With Target
' do nothing if more thatn on cell or out of range
If .Count > 1 Or Intersect(Target, Range("D2:D12")) Is Nothing Then Exit Sub
Set SelRange = Selection
.Select
Evnt = .Offset(0, -1).Text
Application.ScreenUpdating = False
.Parent.ClearArrows
.ShowDependents
'loop though any dependent cells on Target
ArrowNum = 1
'see if selection moves to any dependents
If .Address(External:=True) = .NavigateArrow(False, 1, ArrowNum).Address(External:=True) Then GoTo Done
'otherwise../
Do
'timestamp if on Sheet1
If ActiveSheet.Name = "Sheet1" Then Call TimeStampDependents(Selection, Evnt)
'try another arrow
ArrowNum = ArrowNum + 1
.NavigateArrow False, 1, ArrowNum
' stop if that arrow doesn't exist
Loop Until Err.Number <> 0
Done:
.Parent.ClearArrows
'return to original selection
.Parent.Activate
SelRange.Select
End With
End Sub
This finds which cells change as a result of them altering and the bold line triggers another event macro, as below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelRange As Range, ArrowNum As Long
' expect arrow navigation to fail
On Error GoTo Done
With Target
' do nothing if more thatn on cell or out of range
If .Count > 1 Or Intersect(Target, Range("D2:D12")) Is Nothing Then Exit Sub
Set SelRange = Selection
.Select
Evnt = .Offset(0, -1).Text
Application.ScreenUpdating = False
.Parent.ClearArrows
.ShowDependents
'loop though any dependent cells on Target
ArrowNum = 1
'see if selection moves to any dependents
If .Address(External:=True) = .NavigateArrow(False, 1, ArrowNum).Address(External:=True) Then GoTo Done
'otherwise../
Do
'timestamp if on Sheet1
If ActiveSheet.Name = "Sheet1" Then Call TimeStampDependents(Selection, Evnt)
'try another arrow
ArrowNum = ArrowNum + 1
.NavigateArrow False, 1, ArrowNum
' stop if that arrow doesn't exist
Loop Until Err.Number <> 0
Done:
.Parent.ClearArrows
'return to original selection
.Parent.Activate
SelRange.Select
End With
End Sub
Note that it deliberately tries to find more dependents than there are and relies on the failure to find them to cause an error and to exit the sub. The variable Evnt is declared outside the code and is passed to this code (behind Sheet2) which is almost identical to the Sheet1 macro above but with just three changes in bold below:
Private Sub TimeStampDependents(ByVal Target As Range, Evnt As String)
'Timestamp Data
' TeachExcel.com
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
With Sheet1
'Your data table range
Set myTableRange = .Range("A2:B8")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Column for the date/time
Set myDateTimeRange = .Range("C" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = .Range("D" & Target.Row)
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'### State last two causes of timestamps
With myUpdatedRange.Offset(0, 1)
.Value = Evnt & " changed; " & .Text
If InStrRev(.Text, ";") > InStr(.Text, ";") Then
.Value = Left(.Text, InStr(InStr(.Text, ";") + 2, .Text, ";"))
End If
End With
If you change yellow cell D3 on Sheet2, it will timestamp for the two yellow cells on Sheet1 which use it (and add "Labour rate 2 changed" to column E). For example, the formula in B4 is:
=ROUNDUP(1.3*(2*Sheet2!D3+Sheet2!D8),2)
Likewise green cell D7 affects green cells B3, B7 and B8 of Sheet1 (and will add "Material 2 changed" to column E). Other cells in Sheet2 affect one or no cells (but it doesn't matter, the timestamps should be right).
Hope this helps (and you can modify the approach to suit your purposes).. If so, please remember to mark this Answer as Selected.