auto fill in

spm2002

New Member
Joined
Sep 24, 2002
Messages
2
I am wondering if it is possible in Excel to have a cells value update when an adjacent cell is modified. Specifically
If cell C2 changes I would like D2 to show the time of the change.
I currently have a sheet with the C column using validation properties to limit entries to a list when a cell in the C column is updated or changed I would like the D column to show the time the update was made
I hope this makes sense.
This message was edited by spm2002 on 2002-09-25 20:02
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Welcome spm2002,

If youe excel version is 2000 and later, it works.

<pre>
'Put this code your sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column <> 3 Then Exit Sub
For Each rng In Target
rng.Offset(, 1).Value = Now
Next
End Sub
</pre>
 

spm2002

New Member
Joined
Sep 24, 2002
Messages
2
thanks,
that helps, I have excel 2000 but this formatted sheet will be used by many others and I am not sure what versions they have
any thoughts on a solution that will be compatable with 97 and 2k?

again thank you
sm
 

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Okay sm, Worksheet_Change event of XL97 does not works when validation is used.
If XL97 will be used, it will be solved for the setting "Validation" each time, like this. :)<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column<> 3 Then Exit Sub
Dim strSeq As String
Dim rng As Range
For Each rng In [A1:A10]
strSeq = strSeq & "," & rng.Value
Next
For Each rng In Target
With rng
.Validation.Delete
.Validation.Add xlValidateList, Formula1:=strSeq
End With
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column<> 3 Then Exit Sub
For Each rng In Target
rng.Offset(, 1).Value = Now
Next
End Sub</pre>

_________________
HTH

With Regards,
Colo

bart.gif

This message was edited by Colo on 2002-09-25 22:57
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top