Formula to change date when other cells are modifed

[Guest]

Is there a formula that can be inserted into a cell that will put the current
date in another cell whenever someone edits the contents within a specified
range of cells? For example, A5:M5 contain data, and N5 contains a date.
Column "N" is titled "Last Updated". Therefore, whenever someone changes the
contents contained in A5:M5, then N5 would automatically show the current
date. I'm comfortable in VBA if that's the ultimate answer, but was hoping
for an if/then formula. Would assume the formula could be built in, say O5.
Thank you in advance.

 

[Guest]

A formula can only put the result in its (own) cell so you will need to use
VBA (worksheet event).

 

[Guest]

Thank you. My thought was something like (in O5): If A5:M5 are "CHANGED???",
then N5=datenow(). It could also reside in N5, but didn't think that was
possible. Perhaps there is no formula programming equivalent to capture the
the "Changed" concept. Since I have about 150 rows of projects and each row
would need this formula, I'm not sure how I would write this in VBA. Thanks
for taking time to respond.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo endit
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:M150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Cells(.Row, "N").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If

End If
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

A change to any cell in A1:M150 will place the date/time in N


Gord Dibben MS Excel MVP

 

[Guest]

Gord,

What about if instead of range ("A1:M150") I just want the change in cell
B10 to trigger the date to be put in cell I3 (instead of row N)?

PS: As you will probably notice from my question, I am completely new to VB!

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo endit
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("B10")) Is Nothing Then
Application.EnableEvents = False
Range("I3").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
endit:
Application.EnableEvents = True
End Sub


Gord

 

[Guest]

Works perfectly! Thank you very much.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo endit
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:M150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Cells(.Row, "N").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If

End If
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

A change to any cell in A1:M150 will place the date/time in N


Gord Dibben MS Excel MVP

 

[Guest]

Thank you Gord for helping! Any suggestions where a newbie like me should
start to learn VBA basics? Thanks again.

 

[Gord Dibben]

Try David McRitchie's "getting started with vba and macros" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Note also the links to other sites at page bottom.


Gord

 

[Guest]

Thank you very much for the advice, appreciate it.