I love modifying my own code. I added a new parameter into the parameter
list. for the first project simply put in A1 or the sttartiong column of the
table. For the next project put in D1 or which ever column the table starts.

Function EarliestAction(startcell As Range, ParamArray actions() As Variant)
As String

LastRow = Cells(Rows.Count, startcell.Column + 1).End(xlUp).Row

First = True
EarlistRow = 0
Found = False
For RowCount = 1 To LastRow
For action = 0 To UBound(actions())
If Cells(RowCount, startcell.Column + 2) = actions(action) Then
If First = True Then
EarlistRow = RowCount
Found = True
First = False
Exit For
Else
If Cells(RowCount, startcell.Column + 1) < _
Cells(EarlistRow, startcell.Column + 1) Then

EarlistRow = RowCount
Exit For
End If
End If

End If
Next action
Next RowCount
If Found = True Then
If Cells(EarlistRow, startcell.Column + 1) Now() Then
days = Int(Cells(EarlistRow, startcell.Column + 1) - Now())
Else
days = Int(Now() - Cells(EarlistRow, startcell.Column + 1))
End If
EarliestAction = Cells(EarlistRow, startcell.Column) & _
", " & Cells(EarlistRow, startcell.Column + 2) & ", " & _
days
If days = 1 Then
EarliestAction = EarliestAction & " day"
Else
EarliestAction = EarliestAction & " days"
End If

Else
EarliestAction = ""
End If

End Function


"Ixtreme" wrote:

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