How can I make a column of my data work properly

[master.osgood]

Hi Guys,

Within the document you will see three blue sections (classes) and then a multitude of other classes that are white.

This is the formula within Cell F4

=IF(NOT(ISNA(AA4)),AA4,INDEX(Termone!D$3:E$265, MATCH(D4, Termone!C$3:C$265, 0), MATCH(F$3, F$3:G$3, 0)))

There is a shadow sheet which is Term One results. Within this sheet are the students and classes where students have to resit classes when they get less than 40.

On the main sheet, the white area has the Term Three results and 'some' of the students have resat their exams.

If you play with the sheet you will see that when a new result is put in the term three column for that class, the term one cell is automatically updated and it turns white!

The problem is that I am only wanting students that have been mentioned on my shadow sheet to turn white when there is an update.. and instead I am getting students' cells turning white when they havent even attended term one..

I tried adding an (AND) statement to the formula to make sure they were on the shadow sheet too but it screwed up the whole formula.

SO I am turning to much more intelligent people for help

Thank you!