Agent007 Posted March 3, 2005 Share Posted March 3, 2005 hi all,I have an excel file with 3 colums. The first is to enter an item purchased, the second for the price. Now, everytime I enter an item in the first column, is it possible for the third column to automatically enter the current date without me having to use the TODAY () function manually?Is there some formula which would do the trick?thanks.. Quote Link to comment Share on other sites More sharing options...
hkspike Posted March 3, 2005 Share Posted March 3, 2005 (edited) I guess the lazy way to do would be to useCtrl-;to enter today's date as a custom text in column C. I suspect the =TODAY() function will update anyway?Otherwise how about:Right click the sheet tab and select View Code, then paste this in the window that appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range ' Only look at single cell changes If Target.Count > 1 Then Exit Sub Set rng = Range("B:B") ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Add the Date in Column C whenever an entry is made in the same row in Column B If Target <> "" Then Target.Offset(, +1) = Date ' Clear the cell in Column C if the same row in Column B is cleared If Target = "" Then Target.Offset(, +1) = ""End SubIt will add a static date to column C whenever you enter something in column B - I hope!!Only wish I'd got around to figuring the CODE button.....Andy Edited March 3, 2005 by hkspike Quote Link to comment Share on other sites More sharing options...
crp Posted March 3, 2005 Share Posted March 3, 2005 no formula will do it unless you are willing to have pre-populated rows.You need to use macros or setup a form overlay. Quote Link to comment Share on other sites More sharing options...
Agent007 Posted March 3, 2005 Author Share Posted March 3, 2005 Thing is, I use the spreadsheet on my PDA. Documents to Go doesent have the code feature. I was hoping there was a simpler way..Thanks for the info.. Quote Link to comment Share on other sites More sharing options...
chandru Posted October 19, 2005 Share Posted October 19, 2005 hi agnet007, i think the following method will help u.Right click the sheet tab and select View Code, then paste this in the window that appears: Private Sub Worksheet_Change(ByVal Target As Range)Dim rng As Range' Only look at single cell changesIf Target.Count > 1 Then Exit SubSet rng = Range("B:B")' Only look at that rangeIf Intersect(Target, rng) Is Nothing Then Exit Sub' Add the Date in Column C whenever an entry is made in the same row in Column BIf Target <> "" Then Target.Offset(, +1) = Date' Clear the cell in Column C if the same row in Column B is clearedIf Target = "" Then Target.Offset(, +1) = ""End SubIt will add a static date to column C whenever you enter something in column B - I hope!!Chandru. hi all,I have an excel file with 3 colums. The first is to enter an item purchased, the second for the price. Now, everytime I enter an item in the first column, is it possible for the third column to automatically enter the current date without me having to use the TODAY () function manually?Is there some formula which would do the trick?thanks.. Quote Link to comment Share on other sites More sharing options...
hkspike Posted October 19, 2005 Share Posted October 19, 2005 HmmmmThat looks very like the advice I gave 6 months ago and 8 inches up the page!Andy Quote Link to comment Share on other sites More sharing options...
Tushman Posted October 30, 2005 Share Posted October 30, 2005 HmmmmThat looks very like the advice I gave 6 months ago and 8 inches up the page!AndyAre you sure thats only 8"? Looks more like 16" to me! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.