show date only if information entered in cell

[digitalmuse]

I want to have the current date show up in the first row say A whenever
someone enters information in a row - in other words if there is data in
cell C4 then the current date should show up in row A4

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 Then
If Target.Value <> "" Then
Target.Offset(, -2).Value = Date
Else
Target.Offset(, -2).Value = ""
End If
End If
End Sub

Mike

 

[Pete_UK]

Put this in A4:

=IF(C4="","",TODAY())

Format the cell as date.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

you could try the following formula in cell A4:

=if(C4<>"",today(),"")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[digitalmuse]

Thank you so much works like a charm

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 Then
If Target.Value <> "" Then
Target.Offset(, -2).Value = Date
Else
Target.Offset(, -2).Value = ""
End If
End If
End Sub

Mike

 

[digitalmuse]

thanks for the responses and i guess i should have been clearer, the formula
cant depend on input to cell c4 only it needs to apply to any entry made in
column C

 

[digitalmuse]

which i believe is why the formula now is not working it worked up until row 3
now anything entered in column c after row 3 does not result in a date entry
in colum a

 

[Pete_UK]

Try it this way, then:

=IF(COUNTA(C:C)>0,TODAY(),"")

Hope this helps.

Pete

 

[Mike H]

Hi,

It's written to only work on column C (3)

If Target.Column = 3 Then

If you want to work on a different column change the 3 and the offset to 1
less than the column number. IF you want it to work on a number of columns
then that's a slight re-write

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:F")) Is Nothing Then
Application.EnableEvents = False
If Target.Value <> "" Then
Cells(Target.Row, 1).Value = Date
Else
Cells(Target.Row, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Mike

 

[digitalmuse]

It does work in a cell - im trying now to get it to work in all the cells in
that column(range)

thanks for the assist !

 

[ShaneDevenshire]

Hi,

Try something like this:

=IF(COUNTA(B1:IV1)>0,TODAY(),"")

 

[digitalmuse]

that works perfect in one cell what would i add or modify to have it apply to
an entire colum(or named range) i.e. range is named entryDate and covers
column A

 

[Gord Dibben]

Do you realize that the TODAY() function is volatile and will change when
you open the workbook tomorrow?

I would go with Mike's event code to add a static date in column A whenever
a cell in Column C is changed.


Gord Dibben MS Excel MVP