Excel 2007 : Copy from sheet1 to sheet2 if column y

i want to have it so that info from sheet a copies to sheet b if a column is checked... so like this

A | B | C | D |
----------------------------------------------------------------------------------------------------
Jane Does | cc# | Phone | yes |
----------------------------------------------------------------------------------------------------
Jane Does | cc# | Phone | No |
----------------------------------------------------------------------------------------------------

Sheet 2 i would like to contain any NO in column D

So SHEET2 should look like so.....

A | B | C | D | Notes |
----------------------------------------------------------------------------------------------------------------------
Jane Does | cc# | Phone | NO | my notes |
----------------------------------------------------------------------------------------------------------------------

If you want to do with formulas, you can't add comments to Sheet2, unless you are only adding to the bottom of sheet1 and not changing any existing YES's to NO's.... if you are, then better to add the comments to Sheet1 and pull with the rest of the information....

To extract the relevante info,
In a free column in Sheet1, say at E2 enter formula:

=IF(D2="NO",COUNT(E$1:E1)+1,"")

copied down

then in F2:

=MAX(E:E)

In Sheet2, A2:

=IF(ROWS($A$1:$A1)>Sheet1!$F$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$E:$E)))

copied across the 4 columns and down as far as you want.

The NO in Sheet1!D3 has a space after the word NO... eliminate it.

Also, the formula in Column E should start with =IF(D3="NO",COUNT(E$1:E1)+1,"") at E2... you were off 1 row.