How do I create an automatic 'date last updated' reference?

[Guest]

I would like to create a function that automatically returns the current date
if certain other specific cells are edited, i.e. it is a reference to the
last time cells on that row were updated.

Please help??

 

[Dave Peterson]

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

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

 

[Guest]

Thanks both of you, but neither options seem to update the date when (and
only when) the data in another cell is changed. For example, I would like
cell A3 to display the last date that cell A1 or A2 was updated.

 

[Alec H]

I had a similar problem and Tom solved it for me in an earlier thread
with the following....

Hi,

Latest brainteaser,

I have a Excel 2000 pro workbook, in one of the spreadsheets I have
column (E) that uses a dropdown list (Data/Validation/List). I want the
next column (F) to automatically show the date that the entry to column
E was last changed or if there has been no change show a generic start
date....

Is this possible?

------------------------------------------------------------------------

right click on the sheet tab and select view code. In the resulting
module
paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Cells(Target.Row, 6).Value = Now
Cells(Target.Row, 6).NumberFormat = "mm/dd/yyyy hh:mm"
Target.Offset(0, 1).EntireColumn.AutoFit
End If
End Sub

You can run a onetime macro to fill any empty cells

Sub FillWithGeneric()
Set rng = Columns(5).SpecialCells(xlConstants)
For Each cell In rng
With cell.Offset(0, 1)
If IsEmpty(.Value) Then
..Value = DateValue("01/01/2006") + TimeValue("08:00")
..NumberFormat = "mm/dd/yyyy hh:mm"
End If
End With
Next
Columns(5).AutoFit
End Sub

--
Regards,
Tom Ogilvy


Hope this helps.

Alec.