Enter date in col E when data is entered in cols A-D

[s509ch1]

I have a 4 Column spreadsheet (Cols A-D).
There may or may not be data in each row of those 4 columns.
If there is data in any of the 4 columns in a row, I want the date to
automatically fill in the 5th column.
I do _not_ want that date to change every time Excel is opened...i.e.,
once it's filled it has to "stick".

...possible? If so, how?

Thanks all!

 

[Dave Peterson]

You could use a macro:

rightclick on the worksheet tab that has this behavior, select view code and
paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:d")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If Application.CountA(Cells(Target.Row, "A").Resize(1, 4)) > 0 Then
If IsEmpty(Cells(Target.Row, "E")) Then
Application.EnableEvents = False
With Cells(Target.Row, "E")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
Else
Application.EnableEvents = False
Cells(Target.Row, "E").ClearContents
End If

errHandler:
Application.EnableEvents = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[BrianB]

Depends if number or text data (method 2 works for both). Copy and past
from below.

Method 1 (numeric)
=IF(SUM(A11)<>0,"MyData","")

Method 2
=IF(COUNTIF(A22,"")<>4,"MyData",""

 

[BrianB]

There is a fault where gets converted to an icon.
Range should read A1 : D

 

[s509ch1]

Thank you, I'll give it a try.
cas