Greetings all, i have a new project and im pretty stuck on best way to do what i need to be done.

Basically in sheet "Audit i will populate data in various fields. once completed i need a report.

Sheet 2 "report" Contains a couple of headings that i wish to automatically populate with the data from sheet "Audit" If cell D contains N or NO which signifies non compliance i want for each instance of this to be entered in Report sheet copying over the relevant information to each box and go down the page accordingly.(as a plus if it adds borders even better)

So for example: row 3,5,7 in my example file would be NO for non-compliant and i would then want the report sheet to contain going down all 3 clauses their particular requirement info, details of non compliance and category. (See Sheet "sheet 3" of what it would look like if working right)

Example Book.xlsx

If anyone can help me i would be most appreciative as i have spent hrs trying various things using functions and it seems i need some kind of macro to perform the task...

Kind Regards
Dave.R