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.
Dave
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
I don't believe you can do it directly - you'd need an event routine that alters the validation list using a comma-separated list returned by the function. It might be possible to do it in-cell using windows timers but I suspect the function would only get called when you clicked the dropdown and Excel would probably crash if you tried to change the list then.
dlmille
ASKER
Rorya - I've tried the function in a named range, in the DV list, etc., to no avail, so from your perspective is creating a parallel list beside the original the best approach?
If I can't directly address it in the DV formula (either as a function, or addressing the adjacent almost-duplicate column), then I probably would load a comboBox and drop it on top, lol.