You could always spread your formula across two cells. Put the first five IFs
in your destination cell. For the final FALSE result, put in a reference to
the second cell. In the second cell put a formula with the other IFs. The
second will always return something, but it will only be used if the IFs in
the first cell are all FALSE.

Or, here is an equivalent custom function:

Public Function ShtComment(Rng As Range) As String
Dim WS As Worksheet
On Error GoTo SCerr1
'If Rng contains more than one cell, return ERROR.
If Rng.Count 1 Then
ShtComment$ = "ERROR"
Exit Function
End If
Set WS = Rng.Parent
'If A3 = "" Then
If Len(Range("A" & Rng.Row).Value) = 0 Then
ShtComment$ = vbNullString
'Elseif A3 is found in S3:S20000 more than once Then
ElseIf Application.WorksheetFunction.CountIf(WS.Range("S$ 3:S$20000"),
Range("A" & Rng.Row).Value) 1 Then
ShtComment$ = "Duplicate or Secondary Invoice"
'Elseif H3 is an Error AND J3 = "Yes" Then
ElseIf IsError(Range("H" & Rng.Row).Value) And LCase(Range("J" &
Rng.Row).Value) = "yes" Then
ShtComment$ = "Scheduled to pay/apply"
'ElseIf J3 = "Yes" Then
ElseIf LCase(Range("J" & Rng.Row).Value) = "yes" Then
ShtComment$ = "Paid/Applied"
'ElseIf K3 = "Yes" Then
ElseIf LCase(Range("K" & Rng.Row).Value) = "yes" Then
ShtComment$ = "Dropship import error"
'ElseIf L3 = "Yes" Then
ElseIf LCase(Range("L" & Rng.Row).Value) = "yes" Then
ShtComment$ = "Duplicate or secondary invoice in VNet"
'Elseif E3 ISTEXT Then
ElseIf Application.WorksheetFunction.IsText(Range("E" & Rng.Row).Value)
Then
ShtComment$ = "Open - Dropship"
'Elseif E3 ISNUMBER Then
ElseIf Application.WorksheetFunction.IsNumber(Range("E" &
Rng.Row).Value) Then
ShtComment$ = "Open - Owned Goods"
Else
ShtComment$ = "Open"
End If
Cleanup1:
Set WS = Nothing
Exit Function
SCerr1:
ShtComment$ = "ERROR"
GoTo Cleanup1
End Function

Please note: I haven't tested this function.

Hope this helps,

Hutch

"gardenhead" wrote:

Hello,

I'm trying to write a statement macro and one of the cells is a
comments section where it evaluates other cells in the row. However,
I've exceeded the number of IF statements by one:

=IF(A3="","", IF(COUNTIF(S$3:S$20000,A3)1, "Duplicate or secondary
invoice in GP", IF(AND(ISERROR(H3),J3="Yes"), "Scheduled to pay/apply",
IF(J3="Yes","Paid/Applied", IF(K3="Yes", "Dropship import error",
IF(L3="Yes", "Duplicate or secondary invoice in VNet", IF(ISTEXT(E3),
"Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Goods",
"Open"))))))))

Something a little more readable:

If A3 = "" Then
Print ""
Elseif A3 is found in S3:S20000 more than once Then
Print "Duplicate or Secondary Invoice"
Elseif H3 is an Error AND J3 = "Yes" Then
Print "Scheduled to pay/apply"
Elseif J3 = "Yes" Then
Print "Paid/Applied"
Elseif K3 = "Yes" Then
Print "Dropship import error"
Elseif L3 = "Yes" Then
Print "Duplicate or secondary invoice in VNet"
Elseif E3 ISTEXT Then
Print "Open - Dropship"
Elseif E3 ISNUMBER Then
Print "Open - Owned Goods"
Else
Print "Open"
End If

At first I tried to use two Named Formulas to bypass my way around the
limitation but I kept getting a #VALUE error and I couldn't figure out
why. I don't have any VBA coding experience so I'm wondering if someone
can translate the above into a function? If someone sees a way to make
the original formula more condensed that'd be great too.

Thanks in advance...