It can be done using a helper column

A1:B10 = table

In, say D1: =IF(B1="yes",ROW(),""), copied down to D10
In E1: =IFERROR(INDEX($A$1:$A$10,MATCH(SMALL($D$1:$D$10,ROW()),$D$1:$D$10,0)),1)

Copied down to E10

Create dynamic named range referencing column-E cells
=OFFSET(Sheet1!$E$1,0,0,COUNTIF(Sheet1!$E$1:$E$10,"<>1"),1)