How to conditionally extract rows from one sheet and display them on the next sheet.

[jduzz]

I have just started using excel and have a simple question:
I have a substantial sheet with rows that include a status field (sheet1.status).

I have to extract rows according to two separate conditions:
a) where sheet1.status = ‘X’
b) where sheet1.status = ‘Y’

I want to copy these two datasets into the next sheet, and display them one after the other.
I do not want empty rows within each dataset, and the datasets should be automatically updated whenever I change anything in the first sheet..

How can I do this?

I tried the autofilter option, but this does not seem to work properly.
Any help is very much appreciated!

 

[jbeaucaire]

The AutoFilter is absolutely the right function for this. It not only hides the rows, the resulting filtered data *can* be copied to another sheet, though with an AutoFilter the secondary sheet is often found to no longer be necessary. You can filter the data, deal with the filtered results in place, including copying/printing, etc, then turn off the filter to reveal the full data set when needed.

The only caveat to remember is that the Autofilter expects that EVERY row will have a value in at least one cell, thus the "data set" is contiguous all the way to the bottom in at least that one column. All the others can be blank, but at least one must be filled.