Inserting a date in a cell when another has a value entered

[Kathy_Woodgates]

Hi All

I am new to this community, though have used excel for a good few years, and alothough I have developed some skills with formulas, for some reason I cant get my head around the following:  

I have created a template spreadsheet with drop down options for others to use and I want to add an automatic date/timestamp in one cell on the same row as another if it contains values.  Is there a specific formula I can use for this?

Thank you and regards

Kathy

[#2 · Accepted solution]

@Kathy_Woodgates 

The problem with a formula involving TODAY() or NOW() will be updated continually. You need VBA code instead.

Let's say that you want to add a timestamp in column H when the user enters or changes a value in column D, starting in row 2.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("D2:D" & Rows.Count), Target)
            If rng.Value = "" Then
                Range("H" & rng.Row).ClearContents
            Else
                Range("H" & rng.Row).Value = Now
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Close the Visual Basic Editor.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

[#3]

@Hans Vogelaar 

Thank you so much.  I did encounter this issue with TODAY and NOW.  But by inserting this code it worked immediately once I referenced the cells I wanted it to affect.

Very much appreciated.