We help IT Professionals succeed at work.

Excel:  Populate the current date in a cell, only if data is present in the designated row

usdcaz asked
If there is data in any cell in columns C:J, how can I make the current date populate in the appropriate row in column A?
Watch Question

you can put this formula in appropriate cell in any row in column A


xyz is the value in this example
Most Valuable Expert 2011
Awarded 2010
Hello usdcaz,

do you want column A to populate when any data is entered in C:J? If so, you need to use a macro for a Worksheet_Change event.

The attached macro will put the name in column A as soon as a value is entered in column C to J in the same row. The date will be deleted when there is no cell content in C to J.

see attached file
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:J")) Is Nothing Then
    Application.EnableEvents = False
    If WorksheetFunction.CountA(Range(Cells(Target.Row, 3), Cells(Target.Row, 10))) > 0 Then
        Cells(Target.Row, 1) = Date
        Cells(Target.Row, 1).ClearContents
    End If
    Application.EnableEvents = True
End If
End Sub

Open in new window



Put this formula in column A row 1.
Most Valuable Expert 2011
Awarded 2010


be aware that worksheet functions like =today() and =now() will change when the worksheet is recalculated. So, if you open the worksheet the next day, these cells will show the current computer date, not the date when the data was entered.

The macro I provided will write the date into the cell and that date will not change the next morning. It's a time stamp that shows you the date when the row was last changed.

cheers, teylyn
usdcazSystems Technology Division Manager


THANK YOU!!!  I did realize that it was a Worksheet_Change event that was needed to complete the task, but I was unable to get the code to work as smoothly and functionally as what you provided.  WAY COOL!  Thanks so much again!  ; )
Most Valuable Expert 2011
Awarded 2010

Thanks for the grade.