Is there a way I can write a formula that outputs desired results on a
separate worksheet within a workbook, without showing a bunch of blank rows
for undesired results? For example:

Data is in Sheet1
Sheet2 will display output of only the rows in Sheet1 that meet desired
condition (display all rows of data from Sheet1 where Column F = 'yes').