Cross referencing outcomes are genuine

[ExcelAmateur2014]

Hi

I am looking to find some sort of formula which will check the answers to 10 questions before it an ascertain whether the Overall Outcome is consistent with the answers to the previous questions and if the overall outcome is inconsistent then it would flag the result in Red so that I can easily identify the potential error.

I have uploaded the data to drop box via the below link


https://app.box.com/s/tcvbkidj578zpart4awemlm1f0eecevu


To summarise, SUITABLE outcome can be contradicted by a single NO or UNCLEAR Question before it. UNSUITABLE outcome can only be affected by an UNCLEAR question before it, Unclear question would override everything.

With SUITABLE overall outcome this would be OK as long as there are not any NO or UNCLEAR outcomes in the previous Questions. Or even better would be that it only consists of YES and N/A outcomes and MUST have at least 1 YES in the Question sections
With UNSUITABLE overall outcome this would be OK as long as there are not any UNCLEAR outcomes in the previous Questions. Or even better would be that it only consists of YES, NO and N/A outcomes and MUST have at least 1 NO in the Question sections
With UNCLEAR overall outcome this would only be OK if at least 1 UNCLEAR in the Question sections

Thanks in advance