Per related question, Please see attached workbook.
Please notice in the spreadsheet, I use this function with an INDEX to get the 1 to many results for a criteria-based list, which is then identified by a data validation cell, and all works correct. See attached excel file, the cell to focus on is the yellow one which references column B whose formulas are referencing this function.
Of course, having this "parallel" list in column B is a little awkward, and without doing something fancy with a combobox or something, I'm wondering whether a DV List will accept a comma separated list of values direclty, via a UDF function.
Your assistance would be appreciated in modifying the attached code such that the results can go directly to the DV list. It would be great not to have to have Column B to make this all work.
Function subStrList(kwd As Range, DVList As Range) As VariantDim foundRange As Range, lastRow As LongDim myDVList() As String, cnt As Long lastRow = DVList.Cells(1, 1).Row + DVList.Rows.Count - 1 Set foundRange = DVList.Find(what:=kwd, LookIn:=xlValues, lookat:=xlPart) If Not foundRange Is Nothing Then 'subStrList = foundRange.Value ReDim Preserve myDVList(cnt) As String myDVList(cnt) = foundRange.Value cnt = cnt + 1 Do Set foundRange = DVList.Find(what:=kwd, after:=foundRange, LookIn:=xlValues, lookat:=xlPart) If Not foundRange Is Nothing Then 'subStrList = subStrList & "," & foundRange.Value ReDim Preserve myDVList(cnt) As String myDVList(cnt) = foundRange.Value cnt = cnt + 1 End If Loop While Not foundRange Is Nothing And foundRange.Row < lastRow End If subStrList = myDVListEnd Function