Hi there, I have created an audit sheet and I need it to perfom an automatic task for me. Whenever the answer to a question is 'No' I want the entire row (containing question ID; Question; Reason for fail; required remedial actions etc.) to be copied onto a seperate worksheet, so that all the fails are collected on the same worksheet.



Any help you can give with this would be hugely appreciated. Thank you.

Name your data (without any headings) d and the Yes/No range Question and then enter the following array formula to a separate sheet:

=IFERROR(INDEX(d,SMALL(IF(Question="No",ROW(d)-CELL("row",d)+1),ROWS($1:1)),COLUMNS($A:A)),"")

Please note that this (array)formula must be entered by holding down Ctrl and Shift before pressing Enter.
Copy the formula to the right and downwards as required.

The names d and Question are not absolutely required. I have done it because I find it convenient, but you can, if you prefer, use the actual ranges instead. The formula will then be longer.