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

P.S. Sorry about the duplicate posts about the same subject. The other two
didn't show up in the newsgroup for a long time after I initially posted them
late last night.

"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

Let's say that you want to bring over cells between A2 and G50 from
Sheet1. Enter this formula in A2 of Sheet2

=IF(Sheet1!$H2="Yes",Sheet1!A2,"")

This can be copied across into B2:H2. Highlight the cells A2 to H2 and
copy them down into rows 3 to 50. You should now see entries only in
those cells where H on that row contains "Yes".

If you want the rows bunched up, so that empty rows are not visible,
then highlight column H and click Data | Filter | Autofilter (check) -
from the filter pull-down in H1, select "Yes", and only those rows will
now be visible.

Hope this helps.

Pete

Marie wrote:
P.S. Sorry about the duplicate posts about the same subject. The other two
didn't show up in the newsgroup for a long time after I initially posted them
late last night.

"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

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

Copy C2 across to J2 ..
The above is to sync with the assumption made that Sheet1's data is within
cols A to H. If needed, just copy C2 across beyond J2 to return cols beyond
col H in Sheet1. Then fill down as before.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---