Excel time/date stamp of a cell change

[SemiAuto40]

Hey All, This must be one of the most common questions in Excel but all of the "solutions" that I have found while searching do not work (or I am not implementing properly).

This is the problem: once a value changes in a cell on Sheet1, I need the corresponding cell in Sheet2 to get the Now() value when the cell on Sheet1 was changed. Ex: Sheet1!E6 gets a value - then Sheet2!E6 shows something like 2/17/12 8:45:33.

I want this to be generic enough to apply to a range of cells, not just a one time event.... and such that all of the cells in Sheet2 holding the Now() value don't all display the exact same time like my failed attempts do.

If VB is involved I need specific instructions on where to put in and please phrase it in a 4th grade level.

Please My sanity is at stake here.

 

[dunque]

Not sure if anyone will read this as it is a long time since this thread was active. The various solutons on here were extremely helpful for a problem I have. However, in 'moon on a stock' mode, there were a couple of examples where there was no update - (1) where the value was pasted into the target cell (2) where the existing value in the target cell is deleted.

Also, would there be some variant which would work if the target cell was a formula depending on input from a range of cells ?

 

[CatyH]

Welcome to the Board!

Yes, this definitely requires VBA, specifically, an Event Procedure that is automatically fired when a cell is updated.... the date/time stamp.

THIS IS SOOOOOO AMAZING!!! I LOVE simple code that is logical, efficient and easy-to-follow! I inserted a new tab (instead of Sheet2 I called it DetectChange) and then used a MAX function to find the most recent date which is referenced throughout the workbook so that on any of the pivot table or summary tables, the user will know when the data was most recently updated.

AWESOME!!!! THANK YOU MR. EXCEL for providing this amazing forum... and THANK YOU Joe4 for your reply with the code!

 

[Hunkybuck]

Welcome to the Board!
This variation should do that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 Then Target.Offset(0, -1) = Date
End Sub

I registered on the forum just to say thank you for this. I hardly even had to tweak your code to get it to apply to my needs perfectly. Simple, concise, and doesn't even require a formula, so I can manually edit the date without it breaking the functionality of the auto-timestamp. Add new rows, change sorting -- no problem at all. A tip of the hat to you, sir.

The one minor tweak I made was

If (Target.Column = 4) And (Target.Count = 1) Then Target.Offset(0, 2) = Date

so if you copy a whole section, it pastes unchanged.