Time stamp

[M John]

Near as I can tell the procedures described here:

http://www.mcgimpsey.com/excel/timestamp.html

are for determing when the data entered in the cell changes, but does not
detect whether a formula returns a different result. For example it
correctly indicates when a value is entered into a cell, but for the
"=sum(a1:10)" value entered in B1, the Time stamp does not trigger when new
values are entered anywhere in A1:a10 that changes the sum.

Does the description make sense?

Any help/points for triggering the time stamp procedure in cell b1 when any
of the values in a1:a10 change the sum value in b1?

Thanks,
M John

 

[Billy Liddel]

The time stamp is in the range B2:B10 so for the time stamp of the Sum type:
=Max(B2:B10)

Peter

 

[Tom Hutchins]

One way...

In a VBA module in your workbook, declare a variable to hold the current
value of A1:A10 (on Sheet1 in this example):

Global CurrValue As Double

In the ThisWorkbook module of the workbook, add code to initialize CurrValue
when the workbook is opened, and to check the current sum of A1:A10 versus
CurrValue whenever the workbook is recalculated:

Private Sub Workbook_Open()
CurrValue =
Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("A1:A10"))
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim x As Double
x =
Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("A1:A10"))
If x <> CurrValue Then
With ThisWorkbook.Sheets("Sheet1").Range("B1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
CurrValue = x
End If
End Sub

Hope this helps,

Hutch

 

[Mike H]

If the range you are monitoring has a formula the worhseet change event isn't
fired if the result of that formula changes but would fire if looking at a
cell that caused the change.

For example suppose you are monitoring A1 - A10 to put a timestamp in B1 -
B10 and lets say A1 has the formula =D1+ D2.

If D1 changes then the result of A1 formula will change but the worksheet
change event monitoring that cell will not run.

In short you would have to monitor the cell that caused the change In a1 and
change the timestamp in b1

Have a look here
http://www.vbaexpress.com/kb/getarticle.php?kb_id=530

Mike