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??