| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Date
I have a Spreadsheet that I use to track incoming orders from customers.
Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
Ken
You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes: http://www.mcgimpsey.com/excel/timestamp.html Ken McGonagle wrote: I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
You would want a static date entered.
Event code is required for this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("A" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Gord Dibben MS Excel MVP On Tue, 5 Dec 2006 09:58:00 -0800, Ken McGonagle wrote: I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
Thank you so much!
Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
Ken
Adding a comma and Columns("G;G") so the line reads If Not Application.Intersect(Target, Columns("B:B"),Columns("G:G")) Is Nothing Then Should do it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... Thank you so much! Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
It's not working. once I add the info for G it stops altogether.
"Nick Hodge" wrote: Ken Adding a comma and Columns("G;G") so the line reads If Not Application.Intersect(Target, Columns("B:B"),Columns("G:G")) Is Nothing Then Should do it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... Thank you so much! Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
Ken
Try this Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now End If If Not Application.Intersect(Target, Columns("G:G")) Is Nothing Then Target.Offset(0, -1).Value = Now End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... It's not working. once I add the info for G it stops altogether. "Nick Hodge" wrote: Ken Adding a comma and Columns("G;G") so the line reads If Not Application.Intersect(Target, Columns("B:B"),Columns("G:G")) Is Nothing Then Should do it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... Thank you so much! Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
Nick
That did it. You are the man! Thanks Again. Ken "Nick Hodge" wrote: Ken Try this Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now End If If Not Application.Intersect(Target, Columns("G:G")) Is Nothing Then Target.Offset(0, -1).Value = Now End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... It's not working. once I add the info for G it stops altogether. "Nick Hodge" wrote: Ken Adding a comma and Columns("G;G") so the line reads If Not Application.Intersect(Target, Columns("B:B"),Columns("G:G")) Is Nothing Then Should do it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... Thank you so much! Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Date
How would i use this statement if the criteria is on a different sheet? So
if a value is entered on sheet Joe!H4, then on Sheet3 H4 would have the date stamp? Thanks a bunch! "Nick Hodge" wrote: Ken Adding a comma and Columns("G;G") so the line reads If Not Application.Intersect(Target, Columns("B:B"),Columns("G:G")) Is Nothing Then Should do it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... Thank you so much! Now how would I enter the code if I also wanted to do the same thing for Columns F & G. When something gets entered into G it puts the date in F. "Nick Hodge" wrote: Ken You could use a worksheet_change event. Right click the worksheet tab and select 'view code' and paste this in the resulting window. Close and save, now whenever you enter something in B the date/time stamp will appear in A alongside Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then Target.Offset(0, -1).Value = Now() End If End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Ken McGonagle" wrote in message ... I have a Spreadsheet that I use to track incoming orders from customers. Column A is used for the date I receive the order. Is there a way to say if anything is typed in column B to automatically enter that days date and current time into whatever the corresponding cell is in column A? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto date for contracts | Excel Discussion (Misc queries) | |||
| Auto Date with Manual Entry Protection | Excel Worksheet Functions | |||
| Mileage Claim Formula | New Users to Excel | |||
| auto date script for 2 columns? have 1.. | Excel Discussion (Misc queries) | |||
| Making a auto date entered into a cell permanent from a template | Excel Discussion (Misc queries) | |||
Linear Mode
