Output formula results based on a word in a specific column and row

Question

I'm building a function that uses different formulas based on the DOH of a specific Person in different groups. Some groups may use different formulas depending on the status (if it's active or non-active).

I need the results to be outputted in the Grace Period column, but the grace period column should only have the results for that specific group, and use that group's specific formula.

I also need to take into account that certain groups may use more than one formula depending on status.

ex. Group1 should only output information in the Grace Period column using its own formula and automatically start using Group 2's formula also outputting the results in the Grace Period column. So the formula should change depending on the group name located in column 1.

The formula uses the DOH (the dates located in the column) I want it to output the grace period results without me having to select the specific cell range for them to be outputted

Sub FormTest()
    Dim Client As Range
        Set Client = Columns(1)
    Dim DOH As Range
        Set DOH = Columns(4)
    Dim Status As Range
        Set Status = Column(3)
    Dim Group1 As Range
    Dim Group2 As Range
    Dim Group3 As Range
    
    For Each Client In Group1
        If Client.Value = "Group 1" Then
            Formula2 = "=DOH+30"
        ElseIf Client.Value = "Group 2" Then
            Formula2 = "=DOH+60"
        ElseIf Client.Value = "Group 3" Then
            If Status.Value = "Active" Then
                Formula2 = "=DOH+90"
            ElseIf Status.Value = "NonActive" Then
                Formula2 = "=DOH+30+20"

        End If
End Sub

This really seems like it should be achieved using worksheet formulas, using a lookup table, instead of VBA. — BigBen, Aug 02 '23 at 18:10
@TimWilliams Sorry, I just added a picture of the Excel sheet with the information, but the range will never be constant as one day I may have more of group 1 than group 2. In the current sheet, the range is (A1:A4) but tomorrow it might be (A2:A9). — alxn, Aug 02 '23 at 18:19
BigBen's suggestion is 100% the way to go. A table with three columns: GroupCode, Status, GracePeriod and an XLookUp ( =XLookUp(1,([@GroupCode]=tblLookup[GroupCode])* ([@Status]=tblLookup[Status]),tblLookup[GracePeriod],0) ) formula and you're good to go. This also allows you to administer the Data as needed. So if your Grace Period changes for a Group, all you have to do is update the table, no need to modify VBA or formulas. — Frank Ball, Aug 02 '23 at 19:54

score 0 · Answer 1 · answered Aug 02 '23 at 18:36

As suggested above, you can use a User-Defined Function (UDF) for this.

For example:

Function GracePeriod(grp, status, DOH)
    Select Case grp
        Case "Group 1": GracePeriod = DOH + 30
        Case "Group 2": GracePeriod = DOH + 60
        Case "Group 3"
            Select Case status
                Case "Active": GracePeriod = DOH + 90
                Case "NonActive": GracePeriod = DOH + 30 + 20
            End Select
    End Select
End Function

In E2:
=GracePeriod(A2, C2, D2)

Output formula results based on a word in a specific column and row

1 Answers1