Hello,

I have the following code to retrieve the next upcoming action. The
code works fine and does exactly what I want. However, I have lots of
projects listed in columns and want this formula to be listed under
each project.

The sheet looks like this:

columnA columnB columnC Column
D Column E
Project
A Project B
Phase 1 30-08-2007 Pending
1-10-2007 Pending
Phase 2 04-09-2007 Open
1-08-2007 Draft
Phase 3 01-01-2007 Finished
1-12-2007 Open
Phase X 13-10-2007 Cancelled
13-12-2007 Open

------ function Phase 1, Pending, 0
days Phase2, Draft, -29 Days



Function EarliestAction(ParamArray actions() As Variant) As String

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

First = True
EarlistRow = 0
Found = False
For RowCount = 1 To LastRow
For action = 0 To UBound(actions())
If Cells(RowCount, "C") = actions(action) Then
If First = True Then
EarlistRow = RowCount
Found = True
First = False
Exit For
Else
If Cells(RowCount, "B") < _
Cells(EarlistRow, "B") Then

EarlistRow = RowCount
Exit For
End If
End If

End If
Next action
Next RowCount
If Found = True Then
If Cells(EarlistRow, "B") Now() Then
days = Int(Cells(EarlistRow, "B") - Now())
Else
days = Int(Now() - Cells(EarlistRow, "B"))
End If
EarliestAction = Cells(EarlistRow, "A") & _
", " & Cells(EarlistRow, "C") & ", " & _
days
If days = 1 Then
EarliestAction = EarliestAction & " day"
Else
EarliestAction = EarliestAction & " days"
End If

Else
EarliestAction = ""
End If