All:

I have a simple spreadsheet where a user can enter values into columns
A, B or C. In column D, I would like to place the current date and time
(NOW()) if the data in A, B or C changes in any way. Is there a formula
I can use to do this, or do I need to resort to developing an
add-in/code.

Thanks,
jpuopolo

Hi,

There might be some way to do something along the lines of what you
want via the tracking changes tools built into Excel. I'm not too
familar with those tools. Here is a simple VBA approach to what I think
you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A:C"), Target).Address = "$A:$C" Then
Range("D1").Value = Now
End If
End Sub

Just paste this into the code module for the corresponding sheet, and
format D1 (or whatever cell you want to put the now value into) as the
desired date format, then any time a user enters or deletes a value in
columns A,B,C; D1 will be updated to reflect the time this change
occurs. Note that if A,B, or C contains formulas depending on data
outside of those columns, a change in such external data will trigger a
change in the *computed* value in columns A,B,C without firing this
macro. For that - you would also have to monitor the correpsonding
external inut cells for change.

Hope that helps

-John Coleman
john wrote:
All:

I have a simple spreadsheet where a user can enter values into columns
A, B or C. In column D, I would like to place the current date and time
(NOW()) if the data in A, B or C changes in any way. Is there a formula
I can use to do this, or do I need to resort to developing an
add-in/code.

Thanks,
jpuopolo

John:

Thank you very much! This is exactly what I was looking for.

Best,
jpuopolo

John Coleman wrote:
Hi,

There might be some way to do something along the lines of what you
want via the tracking changes tools built into Excel. I'm not too
familar with those tools. Here is a simple VBA approach to what I think
you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A:C"), Target).Address = "$A:$C" Then
Range("D1").Value = Now
End If
End Sub

Just paste this into the code module for the corresponding sheet, and
format D1 (or whatever cell you want to put the now value into) as the
desired date format, then any time a user enters or deletes a value in
columns A,B,C; D1 will be updated to reflect the time this change
occurs. Note that if A,B, or C contains formulas depending on data
outside of those columns, a change in such external data will trigger a
change in the *computed* value in columns A,B,C without firing this
macro. For that - you would also have to monitor the correpsonding
external inut cells for change.

Hope that helps

-John Coleman
john wrote:
All:

I have a simple spreadsheet where a user can enter values into columns
A, B or C. In column D, I would like to place the current date and time
(NOW()) if the data in A, B or C changes in any way. Is there a formula
I can use to do this, or do I need to resort to developing an
add-in/code.

Thanks,
jpuopolo

John,

I'm glad you liked it - but there is a possible problem with the code.
It works as intended if a user modifies a cell or range of cells
entirely contained in columns A:C. But - if a user were to (for some
strange reason) select say A3:E6 and do something like hit delete, then
the time stamp won't be updated even though the target of the change
effects cells in Columns A:C

If this is an issue, you can substitute the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:C"), Target) Is Nothing Then
Range("D1").Value = Now
End If
End Sub

In practice the 2 subs will probably function the same from your proble
description - but for some reason the issue popped into my mind when I
was Christmas shopping this afternoon.

-John

john wrote:
John:

Thank you very much! This is exactly what I was looking for.

Best,
jpuopolo

John Coleman wrote:
Hi,

There might be some way to do something along the lines of what you
want via the tracking changes tools built into Excel. I'm not too
familar with those tools. Here is a simple VBA approach to what I think
you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A:C"), Target).Address = "$A:$C" Then
Range("D1").Value = Now
End If
End Sub

Just paste this into the code module for the corresponding sheet, and
format D1 (or whatever cell you want to put the now value into) as the
desired date format, then any time a user enters or deletes a value in
columns A,B,C; D1 will be updated to reflect the time this change
occurs. Note that if A,B, or C contains formulas depending on data
outside of those columns, a change in such external data will trigger a
change in the *computed* value in columns A,B,C without firing this
macro. For that - you would also have to monitor the correpsonding
external inut cells for change.

Hope that helps

-John Coleman
john wrote:
All:

I have a simple spreadsheet where a user can enter values into columns
A, B or C. In column D, I would like to place the current date and time
(NOW()) if the data in A, B or C changes in any way. Is there a formula
I can use to do this, or do I need to resort to developing an
add-in/code.

Thanks,
jpuopolo