One way to set it up dynamically using non array formulas ..

Assume source data in Sheet1's cols A to H, data from row2 down. The key col
is col H which contains for eg: Yes, No, etc

In Sheet2,

Assume A1 will house the desired input for the key col H, say: Yes

Put in B2:
=IF($A$1="","",IF(Sheet1!H2=$A$1,ROW(),""))

Copy B2 down to cover the max expected extent of data in Sheet1's col H, eg
down to B500. Leave B1 blank.

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(Sheet1!A:A,MATCH (SMALL($B:$B,ROW(A1)),$B:$B,0)))

Copy C2 across to J2, then fill down by the smallest extent sufficient to
cover the max expected number of result lines for any input in A1, say down
to J100. Hide away col B. Cols C to J will return the required results from
Sheet1's cols A to H, with all result lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie" wrote:

I would like to set up a workbook with two worksheets:
- Sheet1 will have numerous rows of data
- Sheet2 will display only those rows from Sheet1 that meet a specified
condition, e.g. Column H in Sheet1='yes'
I'd like to accomplish this without displaying a bunch of blank rows that
don't meet the desired condition. Is there a formula or a way to do
this?
Thanks