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.

 

[tobiasdon]

looking at your first post on this thread. when SemiAuto40 changes a value on sheet one it inserts the date/time stamp on sheet 2. what I am trying to do is this, if the original cell from sheet one is cleared out I also want it to clear the previously inserted information on sheet 2.

 

[Joe4]

This should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myAddress As String
 
'   Only run when a single cell is updated
    If Target.Count = 1 Then
'   Capture the address of the cell being updated
        myAddress = Target.Address
'   If the length of the cell updated is zero, remove the datestamp from sheet2
        If Len(Target) = 0 Then
            Sheets("Sheet2").Range(myAddress).ClearContents
'   Otherwise add the date/time stamp to the corresonding cell on sheet 2
        Else
            Sheets("Sheet2").Range(myAddress) = Now()
        End If
    End If
 
End Sub

 

[tobiasdon]

That is awesome, I see the logic now. I posed this question in hopes that I would be able to figure out how to use the logic to fix my thinking processes. Although I see it here now, I need help. Same question, different situation. Now, I need to do the same thing but the data would be inserted in range A148:156 and the date/time stamp would appear and disappear in range N148:N156.

 

[Joe4]

First, check to see if the cell being updated is in your specified range (using Intersect).
Then you can use the Offset function. Since the letter N is 13 away from column A, you would offset the cell just updated by 13 columns, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myAddress As String
     
'   First check to see if the cell updated is in the range "A148:A156"
    If Not Intersect(Target, Range("A148:A156")) Is Nothing Then
'   Only run when a single cell is updated
        If Target.Count = 1 Then
'   If the length of the cell updated is zero, remove the datestamp from column N
            If Len(Target) = 0 Then
                Target.Offset(0, 13).ClearContents
'   Otherwise add the date/time stamp to the same row in column N
            Else
                Target.Offset(0, 13) = Now()
            End If
        End If
    End If
 
End Sub

Make sense?

 

[tobiasdon]

yes, that is so great!!! I just got done throwing a 15 minute party. Joe you are awesome! Thank you!

 

[Joe4]

You are welcome. Glad to help!

 

[ChrisHaley81]

Hi All,

I could really do with some help as apparently my brain has decided it doesn't want to work, I want to do it so that when a "Y" is entered into column E that in F it puts a date stamp to confirm when the work was completed.

Thanks

 

[Joe4]

I could really do with some help as apparently my brain has decided it doesn't want to work, I want to do it so that when a "Y" is entered into column E that in F it puts a date stamp to confirm when the work was completed.

Boy, people really love tacking on to this old thread, don't they?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
     
'   First check to see if the column E is the column being updated
    If Target.Column = 5 Then
'   Only run when a single cell is updated
        If Target.Count = 1 Then
'   If the entry in column E is "Y" add a datestamp in column F
            If Target = "Y" Then
                Target.Offset(0, 1) = Now()
            End If
        End If
    End If
 
End Sub

 

[Lewie]

[TABLE="width: 500"]
<tbody>[TR]
[TD]As columns A:D are populated with what stage a drawing is at, I want the date to automatically appear in E 2:5. I only need the date for the right most cell at each instance.


Edit: I would also like column F to display the right most person initials.


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Designed
[/TD]
[TD]Checked
[/TD]
[TD]Engineered
[/TD]
[TD]Approved
[/TD]
[TD]Date[/TD]
[TD]Person
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Designed Date[/TD]
[TD]BLE
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]No Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BLE[/TD]
[TD]ATI[/TD]
[TD]JAG[/TD]
[TD][/TD]
[TD]Engineered Date[/TD]
[TD]JAG
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BLE[/TD]
[TD]GFB[/TD]
[TD][/TD]
[TD][/TD]
[TD]Checked Date[/TD]
[TD]GFB
[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Lewie,

I have been out of town all week, but see you asked the question over here: http://www.mrexcel.com/forum/excel-questions/874313-adding-date-modified-cell-each-row.html
Since we do not want two active threads on the same question, and you have had activity on that thread, let's keep that other one as the active one instead of this one.