Dynamic DropDown list from Unsorted vector

[RegorTheGreat]

This seems to be a fairly common question I have found...with no answer!
Maybe I just missed the answer somewhere, but I am hoping to get a
quick, direct, answer. I simply want my dropdown list to contain an
array of fields from a column A if column B = "Yes". The problem is
the that my rows are not sorted.

Example:
0 A B
1 *num* *YN*
2 one Yes
3 two No
4 thre Yes



On the same sheet, I have a cell which does Data Validation List. I
just want a simple code for Source: to create the dropdown list that
will only list (one, thre).
I have gotten this to work, partially, a couple different ways...but
nothing I have found works perfectly. It doesn't seem like it should
be a complicated task...

This code will only return a direct vector in order = returns 2 rows
starting from first row of matched item. Returns (one, two)...thus
requires a sorted list.
=OFFSET(B1,MATCH("Yes",B1:B4,0)-1,-1,COUNTIF(B1:B4,"Yes"),1)

This code will return only the first found single entry. Returns
(one). Not exactly much of a list...
=INDEX(A2:B4,MATCH("Yes",B2:B4,0),1)


I would like to do this without having to dynamically create another
list along side my table to show all "Yes" items only, leaving the rest
blank to let excel
Ignore Blank cells.
I am fine with using a macro...but just not sure how I would do it. If
there was a way to write the Data Validation completely as a VB macro,
and not use the built in Data Validation, I could probably just use an
IF statement to throw the range into an array, then populate the list
from that... I don't know how to mix VB macro variables with the Data
Validation, if that is possible.

My actual sheet has well over 100 rows with 8 columns and multiple
sheets in which this sample can be used to help me fix my full sheet.

I am sure there is a simple answer and I am just missing it. But
through all the searching online, I haven't yet found a clear cut
simple answer! Any help is greatly appreciated.

 

[joel]

Have you used autofilter method on column B. Yo can then us
Specialcells method using the visible option to get the selected items.


With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("B:B").AutoFilter
.Columns("B:B").AutoFilter _
Field:=1, _
Criteria1:="yes"
Set SelectedCells = _
.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible)
SelectedCells.Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With