ConcatenateIf loop in macro then output value

Hi - thanks in advance for any guidance/solutions! Looking to translate the UDF ConcatenateIF into a loop in a macro.

So I have an applicability matrix that is updated on a recurring basis - dummy workbook provided. Basically, does Report1 apply to Section2, etc. If it applies, the user inputs an "X" in the column. When the user is reviewing Section2, they'll need to know what reports they need to obtain. So in the summary tab I basically need to populate the applicable reports next to each section.

I was using the UDF ConcatenateIf to do this; however, we need to keep auto-calc update on in the workbook/sheet so it continues to recalculate when someone makes a change to any cell in the w/b. With all this processing, the user will inadvertently hit escape as the UDF is running and interrupt the code (prompt tells them so, user thinks they broke the template).

To get around this, I think I need to loop the ConcatenateIf function into a macro that is called by a button, and output the results of the ConcatenateIf. Any thoughts on accomplishing this? Basically, column B in Summary needs to be the output of ConcatenateIf - this will keep the UDF from running continuously.

UDF used:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant

Dim i As Long
Dim strResult As String
On Error GoTo errHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).value = Condition Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
errHandler:
ConcatenateIf = CVErr(xlErrValue)
End FunctionConcatenateIf Output.xlsxConcatenateIf Output.xlsx

Can you elaborate on how exactly this work? Wouldn't this still cause the cells with the ConcatenateIf formulas to continuously update?

This function is only called when a cell value is changed. So in principle Concatenateif executes the changes for the cell only... maybe I was careless about the parameters but the scope range is the cell only..

Due to the other processing required for the greater workbook, this is still causing an issue. Is it possible to translate the ConcatenateIf function to VBA so we can loop it within a macro and essentially paste values in the selected output column? That loop would be within a macro that runs from a command button.

Thanks rcm, this was exactly what I was looking for!

you are most welcome, please mark the thread as solved!!