Remove blank rows on an IF/ISNUMBER/SEARCH formula

[dezibluenose]

Hope someone can help here.

I'm creating a sheet where if a certain cell shows (for example) Yes, all the details from that row get pulled back onto a separate sheet.

I've used an IF/ISNUMBER/SEARCH combo and it seems to work however, when using this, on my second sheet it's leaving blank rows. So if row one fits my criteria it shows details but row 2 is left blank. If row 3 or 4 fit my criteria the details are pulled back but then I'm left with blank rows.

I don't want to leave a row blank. Is there a way I can change my formula so that if the second row doesn’t fit my criteria it pulls back what's in the next row and so on and so on?

The attached picture probably explains what I'm trying to do better.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=FILTER(A16:C18,D16:D18="Yes")

 

[dezibluenose]

Hi & welcome to MrExcel.
How about

=FILTER(A16:C18,D16:D18="Yes")

Absolutely fantastic - worked perfectly.

Show's there is always something new to learn. Thanks again. Really appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback.