Function CatNames(Target As Range, Dates As Range)
'Start Date is 1st parameter in Dates
StartDate = Dates(1)
'End Date is 2nd parameter in Dates
EndDate = Dates(2)

CatNames = ""
'Check each Row in Target range for dates
' between start and end dates
For RowOffset = 1 To Target.Rows.Count
'check if start date in Row is greater then
'selected start date and less than selected end date
'or
'end date in row is greater than start date
'and less the selected end date
If (Target(RowOffset, 2) = StartDate And _
Target(RowOffset, 2) <= EndDate) Or _
(Target(RowOffset, 3) = StartDate And _
Target(RowOffset, 3) = EndDate) Then

'if the dates are in range then Catenate the
'data in column A
CatNames = CatNames & "," & Target(RowOffset, 1)
End If
Next RowOffset

End Function


"Pete" wrote:

Thanks Joel. I'm a beginner with VBA. Would it be possible to add comments
to your function so that I can better understand the logic?

I assume that I need to replace Dates(1) and Dates(2) with actual dates?

"Joel" wrote:

You can use a UDF function like below


call with
=CatNames(A1:A100, FY!E3:E4)



Function CatNames(Target As Range, Dates As Range)
StartDate = Dates(1)
EndDate = Dates(2)

CatNames = ""
For RowOffset = 1 To Target.Rows.Count
If (Target(RowOffset, 2) = StartDate And _
Target(RowOffset, 2) <= EndDate) Or _
(Target(RowOffset, 3) = StartDate And _
Target(RowOffset, 3) = EndDate) Then

CatNames = CatNames & "," & Target(RowOffset, 1)
End If
Next RowOffset

End Function



"Pete@cadth" wrote:

Hi,

I have 3 columns and multiple rows.
Column A contains the product name
Column B contains the start date
Column C contains the completion date

I would like to concatenate all the product names within a specified date
range into 1 cell. I already have my date range identified in another tab
(i.e. FY!E3:E4)
Any ideas how I can do this?

Thanks.