Automatic Date?

[Domigos]

I am trying to find a way of automatically inserting a date when a second cell has been altered. For example A1 = blank, if I then put any entry in A2, I want to show the date in A1 that A2 was altered.

I have already used the IF function using the NOW() function as an argument - but of course the date always changes thereafter whenever the workbook is opened or recalculated, as the cell is constantly reflecting (correctly) the NOW() function.

I just want to know when a cell was changed - sounds too simple to be difficult but I can't find a way of doing it. Can anyone help me with this?

[Duane]

Create a macro that goes to your date cell A1 and enters =today() whenever a user types anything into A2.

Make sure to add another step in your macro to CopyPasteValues in the date cell so that the today formula is not there to update.

Finally, create a button called "datestamp" and place it next to A2; assign your datestamp macro to it - users should hit it after updating.

There may be better ways with better VBS coding, but this is a simple way that unfortunately depends on your user actually hitting the update button after entering data.


[ This Message was edited by: Duane on 2002-03-21 13:38 ]

[Jack in the UK]

will ctrl+: not do the job>>>>>

[kinkyparamour]

Try this and see if it will work for you....

=if(isblank(a2),"",if(b2="",now(),b2))

Adjust cells to fit.............


Denny

[Domigos]

Thanks to you all for your efforts at solving this one - and I understand the approach that all of you have made, but I still have the problem.

Perhaps I was beig over-simplistic in my original description.

What I want to do is to have a list which automatically keeps a running total of a stock item ( I have already solved this part) but then I would like to have a column alongside the running total which tells me the date at which an adjustment to the stock level had been made. Is this any clearer?

[Tommy Bak]

You could place a macro like this i the sheets own module. (rightclick on the sheettab, choose view code, and insert this code)
Please adjust Target.column yourself (here 2 = Column B)
Whenever a change is made to column B todays date is inserted in column C and it is not automatic updated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Target.Offset(0, 1) = Date
End Sub

regards Tommy

[Domigos]

Thanks Tommy, I'll certainly give that a try.

Rgds,
Richard

[Joe Was]

Tom, I like your clean, compact solution to the problem. Joe Was

[Domigos]

Tommy,

What can I say? Your response works just perfectly - by adding additional lines of code, I can now format any columns on the same worksheet to behave in exactly the way I required - pure brilliance as far as I'm concerned.

I really do appreciate your help in solving this problem which, prior to this, I worried at for about three weeks!

Once again many thanks for sharing your undoubted talent and experience.

Richard