if an answer is correct copy question to different sheet

moley165

New member
Joined
Sep 9, 2013
Messages
3
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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).
 
Top