Auto populate a field with a date when other field is selected from a validation list

[AusSteelMan]

Hi everyone,

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub. I don't know how to do this and am hoping you could help me.

The information is currently part of a Table in Excel 2007 that has about 500 records.

Thankyou to everyone who takes the read my post and to those who can help me.

IdeasList

*	J	K
*
*	*
*	*

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 80px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Month Completed[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]In Progress[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Complete[/TD]
[TD="align: center"]Jul-13[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</TBODY>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 J4 List * =ValidationList_Status * K4 List * =ValidationList_Months * <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[apo]

Hi..

Add this to your sheet code..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As String
If Target.Column <> 10 Then Exit Sub
If Target.Value = "Completed" Then
a = Target.Address
Range(a).Offset(0, 1).Value = Format(Now, "long date")
End If
End Sub

 

[AusSteelMan]

Thanks very much apo

Great simple code

Darren