Extracting Data Based on Criteria

It's a little ugly, but it works......

Assuming your sheets are named Sheet1 - Sheet3 and your data is in column A to Column D...where Column D contains the Status....and as you implied, that the data to extract is in Rows 5:20, then try:

=IF(ROWS($A$1:A1)<=COUNTIF(Sheet1!$D$5:$D$20,"closed"),INDEX(Sheet1!A$5:A$20,SMALL(IF(Sheet1!$D$5:$D$20="closed",ROW(Sheet1!$D$5:$D$20)-ROW($A$5)+1),ROWS($A$1:INDEX(A1:A100,ROW()-ROW(A1))))),IF(ROWS($A$1:INDEX($A$1:$A$100,ROW()-COUNTIF(Sheet1!$D$5:$D$20,"closed")-1))<=COUNTIF(Sheet2!$D$5:$D$20,"closed"),INDEX(Sheet2!A$5:A$20,SMALL(IF(Sheet2!$D$5:$D$20="closed",ROW(Sheet2!$D$5:$D$20)-ROW($A$5)+1),ROWS($A$1:INDEX($A$1:$A$100,ROW()-COUNTIF(Sheet1!$D$5:$D$20,"closed")-1)))),IF(ROWS($A$1:INDEX($A$1:$A$100,ROW()-COUNTIF(Sheet1!$D$5:$D$20,"closed")-COUNTIF(Sheet2!$D$5:$D$20,"closed")-1))<=COUNTIF(Sheet3!$D$5:$D$20,"closed"),INDEX(Sheet3!A$5:A$20,SMALL(IF(Sheet3!$D$5:$D$20="closed",ROW(Sheet3!$D$5:$D$20)-ROW($A$5)+1),ROWS($A$1:INDEX($A$1:$A$100,ROW()-COUNTIF(Sheet1!$D$5:$D$20,"closed")-COUNTIF(Sheet2!$D$5:$D$20,"closed")-1)))),"")))

The formula has to be confirmed with CTRL+SHIFT+ENTER, not just ENTER.... you will see {} brackets appear around the formula. You can then copy the formula down and across as far as necessary....

Note: VBA may be a better way to go...