Compare Multiple Rows by Individual column across multiple sheets

Why have all those worksheets? Why not combine all the data onto one worksheet?

Take a look at Sheet1 where I have combined all the data and you can use the filters to draw out the data that you want.

File SWM Analysis - ndm(2)

Take a look at the formulae that I used and compare them with what you have in your original workbook.

I changed your formulae to eliminate the errors that were being displayed by trying to eliminate them at "source" so that they would not show error values all over your worksheet.

File SWM Analysis - Conditional Formatting amended - ndm

I discovered many conditional formats that were in small areas that appeared to be covered by other conditional formats for larger areas. They all seemed to follow the same conditions (without examining every one of the huge number of formatting rules)so, I selected the whole area covered by the conditional formatting and made one set of rules apply to the entire area. I may be in error doing this but it may be worth a look to see if it is what you want. The formulae are the same as in the other workbook.

If you take the time to go through your Conditional Formatting rules, you want to be sure that you are not applying more than one set of rules to an area of the worksheet.

The benchmark of 80% as set out in your original formula was for an A and not an A+ IF(AND(D24>=80,D24<90),"A", A+ starts at 90% AND(D24>=90, D24<=100),"A+"

The nested IFs were defining a lower and upper limit for each grade. This can be accomplished by using a lookup table of numeric grades with letter equivalents or it can be done with a lookup formula that incorporates the grades and assigns the Letter grade. I chose to use the LOOKUP and the formula =IF(D2="","",LOOKUP(D2,{0,50,60,70,80,90,"NA";"C","C+","B","B+","A","A+","NA"})) does that. It looks up the grade in D2 then finds where that grade is in the list started with a { . The grades are in order of lowest to highest. There is a ; in the middle of the listing and this divides the values being looked up from the values to be returned. The values being returned are in the order of the values being looked up. This means that if the value looked up is the third value, then the third value after the ; will be returned.

If you are wanting to read this formula to understand it, I will try and take it apart for you.

=IF(D2="","",LOOKUP(D2,{0,50,60,70,80,90,"NA";"C","C+","B","B+","A","A+","NA"}))

=IF(D2="","", This part is to eliminate unnecessary computation where there are no values to compute....if D2 is blank enter "" which is a null cell...shows nothing but isn't really blank and the computation of the rest of the formula stops if it is the TRUE part of the IF formula. A blank cell will be TRUE when D2 is empty.

LOOKUP(D2,{0,50,60,70,80,90,"NA";"C","C+","B","B+","A","A+","NA"})) This part looks up the value of D2. Is the value >=0 and less than 50, is the value >=50 and less than 60 etc. If the grade is between 50 and 59 that falls into the 2nd range therefore a C+ which is the second grade in the values to be returned would be the answer is this case would be C+.