Show when a student has a mark updated in a column with their orginals

[master.osgood]

Hi there,

Attached there is a sheet I have been working on but blank.

The blue coloumns indicate First Term Results from students. What I would like to do is when students resit failed marks from the first term the first term section (blue area) gets updated and turns the cell white to indicate a new mark.

As you can see I already have a formula in there

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

But unfortunately this doesn't account for a everything.

When the term one result is #N/A (a student didnt sit it) the mark from term 2 is automatically put in place of the term one result making it look as though a student failed that assignment.. when they obviously didn't they just took it in a different term.

Also, the formula takes data from the new results but it cannot ignore new data even if it is #n/a so the first term cells are always replaced even if the student doesnt need to resit it (they got more than 40)

If anyone could help I would obviously be eternally grateful as with a decent formula I am hoping the conditional formatting will start working properly too.

Thanks.


(NS stands for Non submitted by the way