Require a return of Complete or incomplete base on information in a cell

I have to put together a speedsheet that will allow me to return a complete or incomplete base on certain criteria’s of each cell. The example below represents the information that could go into each cell. A1 thru A3 is not restricted and can have either school listed in them. The DOC is restricted to either “YES” or “N/A” base on the school.

SCHOOL DOC#1 DOC#2 DOC#3 DOCS#4
A1=NPS B1=YES C1=N/A D1=YES E1=YES
A2=NIU B2=N/A C2=YES D2=N/A E2=YES
A3=JOCCP B3=YES C3=N/A D3=YES E3=YES


This is an example above is what I have to put together. It should give you a good idea.

This formula below is the actual formula and it works..however, IT ONLY WORK FOR THAT ONE SCHOOL, "NPS". What I need for this formula to do is:

=IF(AND(B17="NPS",E17="YES",F17="N/A",G17="YES",H17="YES",I17="YES",J17="YES",K17="YES"),"COMPLETE","INCOMPLETE")

for A1, I must be able to select any school and base on the school selected, have the required DOC be correctly annotated. For example...base on the formula above, on the same row...if I change B17 to "NIU", I need the appropriate "YES" or "N/A" be reflected as in the example. And I need for the formula to work on each line in that way. IS THIS POSSIBLE??

Just remove the first condition in the AND function, or change it to ensure that the cell is not blank, like this:

Formula:

=IF(AND(E17="YES",F17="N/A",G17="YES",H17="YES",I17="YES",J17="YES",K17="YES"),"COMPLETE","INCOMPLETE")

or:

Formula:

=IF(AND(B17<>"",E17="YES",F17="N/A",G17="YES",H17="YES",I17="YES",J17="YES",K17="YES"),"COMPLETE","INCOMPLETE")

Hope this helps.

Pete

Pete,

Thank you very much. This really helped. This is my one question, the formula work but for one line or one school. In reference to the second formula above, cell B17 has the option to have at least 6 different schools listed in it. And base on the school, the "YES" or "N/A" can change. Is there a way to make it so that when a specific school is inputted, the correct YES or N/A will reflect that school listed in cell B17

It would help if you could post an example workbook, as I have to guess a lot working from just one formula. The FAQ describes how to attach a file - basically, while you are posting click on the Go Advanced button and then you can use the paperclip icon or the Manage Attachments button below your text.

Hope this helps.

Pete

In the attached example, this is the data that is needed. The programs listed in cell B3 to B12 along with the documents that is needed for each of them. for example, with the formaula you sent, it will work for the first B3 "NPS" only.. However, I need a formula so that no matter what school I input in B3 to start it off, it will reflect the correct documents needed for that school and so. So in other words, when I change the school in B3, the documents require will also change to reflect the school.

This hope this can bring you closer to a solution
//Ola

I have used 'Conditional formatting' in cell E17:O18, and 'Data validation' in B17:B18.
The formula in E18 can be erased. I kept it just to as an example.