I have the following formula:

=IF(ISERROR(VLOOKUP(RC1,Outstanding_issues,3,FALSE)),if(iserror(VLOOKUP(RC1,Closed_issues,3,FALSE)),"",if(len(VLOOKUP(RC1,Closed_issues,3,FALSE))>40,CONCATENATE(LEFT(VLOOKUP(RC1,Closed_issues,3,FALSE),40),"..."),VLOOKUP(RC1,Closed_issues,3,FALSE))),IF(LEN(VLOOKUP(RC1,Outstanding_issues,3,FALSE))>40,CONCATENATE(LEFT(VLOOKUP(RC1,Outstanding_issues,3,FALSE),40),"..."),VLOOKUP(RC1,Outstanding_issues,3
,FALSE)))

"Outstand_issues" and "Closed_issues" are both named dynamic ranges
defined w/ the following formulae, respectively:
=OFFSET(Outstanding!R4C1,0,0,COUNTA(Outstanding!R4C1:R5977C1),30)
=OFFSET(Closed!R4C1,0,0,COUNTA(Closed!R4C1:R5977C1),30)

I would like to replace this with a user defined function. Tip of $10
(or tell me what's reasonable) if you can provide the custom function
yourself. The amount listed is for instructions on how to convert the
formula myself without rewriting in VB.

This seems to do the trick. You'll need to create a new module in VBA
and add this code to it; it won't be visible to the worksheet if you
just dump it in the workbook or worksheet code sheets (don't ask me
why; you'd think of all places the worksheet could see, its own code
would be at the top of the list).

Once you've done that and saved the workbook, you should be able to
use it in a formula by simply entering "=IssueText(RC1)".

Some caveats: Since this is a user-defined function, Excel no longer
recognises that the result of the formula depends on the values in the
Closed_issues and Outstanding_issues ranges. You can recalculate cells
manually (F2 and Enter) or you can recalculate a whole sheet
(Shift-F9) or all sheets (F9). Alternatively, if you must have
automatic recalculation, you can use VBA to trigger one when the data
is updated; but that gets messy, so I'mnot including it here.

Please let me know if you have any problems with this. Good luck!

Manuka-ga



Option Explicit

Function ChopString(S As String) As String
Const MaxLen = 40
    If Len(S) > MaxLen Then
        ChopString = Left$(S, MaxLen) & "..."
    Else
        ChopString = S
    End If
End Function

Public Function IssueText(Cell As Range) As String
Dim Closed_String As String, Outstanding_String As String

    With Application.WorksheetFunction
        On Error GoTo NotClosed
        Closed_String = .VLookup(Cell, Range("Closed_issues"), 3, False)
        On Error GoTo NotOutstanding
        Outstanding_String = .VLookup(Cell, Range("Outstanding_issues"), 3, False)
        On Error GoTo 0
    End With
    
    Closed_String = ChopString(Closed_String)
    Outstanding_String = ChopString(Outstanding_String)
    
    If Outstanding_String = "" Then
        IssueText = Closed_String
    Else
        IssueText = Outstanding_String
    End If
    
    Exit Function

NotClosed:
    Closed_String = ""
    Err.Clear
    Resume Next

NotOutstanding:
    Outstanding_String = ""
    Err.Clear
    Resume Next

End Function

Thanks very much. I was travelling for work and was unable to log in
and check the answers, but if this works it will be perfect.

Thanks,

Josh

manuka - I apologoze for ignoring this. Please post an answer and I'll
add on a tip.

Thanks,