Formula to change date when other cells are modifed
 

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.

Formula to change date when other cells are modifed
 

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

"Mapasque" wrote:

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.

Formula to change date when other cells are modifed
 

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.

"Toppers" wrote:

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

"Mapasque" wrote:

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.

Formula to change date when other cells are modifed
 

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

On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque
wrote:

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.

"Toppers" wrote:

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

"Mapasque" wrote:

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.

Formula to change date when other cells are modifed
 

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!
--
Merci!
Bonzai


"Gord Dibben" wrote:

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

On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque
wrote:

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.

"Toppers" wrote:

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

"Mapasque" wrote:

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.

Formula to change date when other cells are modifed
 

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

On Wed, 20 Jun 2007 18:30:00 -0700, Bonzai
wrote:

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!

Formula to change date when other cells are modifed
 

Works perfectly! Thank you very much.

"Gord Dibben" wrote:

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

On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque
wrote:

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.

"Toppers" wrote:

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

"Mapasque" wrote:

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.

Formula to change date when other cells are modifed
 

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


"Gord Dibben" wrote:

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

On Wed, 20 Jun 2007 18:30:00 -0700, Bonzai
wrote:

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!

Formula to change date when other cells are modifed
 

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

On Thu, 21 Jun 2007 10:25:01 -0700, Bonzai
wrote:

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

Formula to change date when other cells are modifed
 

Thank you very much for the advice, appreciate it.
--
Merci!
Bonzai


"Gord Dibben" wrote:

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

On Thu, 21 Jun 2007 10:25:01 -0700, Bonzai
wrote:

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