Excel - Insert date based upon adjacent cell content

Question

Is there a way to insert the current date (Now() or Today() ) based upon an update to the adjacent cell content.  (e.g. when data is added to the cell for the first time, another cell shows todays date).

I tried using the following: "=IF(A1="","",IF(B1="",NOW(),B1))" but this gives a circular reference error and does not work.  Funny, it worked fine in Excel 2003.

The idea is to track when a particular entry to the spreadsheet is made.

Windows XP, SP3, Office 2007

Answer 1

Hi,

That wouldn't have worked in E2003 it would give a circulare reference. Put this formula in B1

=IF(A1="","",NOW())

However, I don't think that what you want because it will update when Excel calculates. so try this macro. Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then

    Application.EnableEvents = False

        Target.Offset(, 1) = Now

    End If

    Application.EnableEvents = True

End Sub

Answer 2

That works perfectly!  Thank you you for your help.