if an answer is correct copy question to different sheet

[moley165]

all,

i have a questionaire that has yes/no answers basically i want some way of copying the question over to a different sheet if it answered yes so that on sheet 3 i will only have questions that were answered yes

kind regards

ricky

[NBVC]

Assuming the questions are in Sheet1, starting in A2 down to A100, and the Yes/No replies are starting in B2 down to B100, then in the other sheet enter formula like:

=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="YES",ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS($A$2:$A2))),"")

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER, then copy down as far as needed to get all possible YES results (i.e copy down 100 rows in this case).