Trying to create a school grade sheet in order to check progress.

Hello,

I am trying to create an excel spreadsheet that can show me if a child is performing above predicted, on target grade or below it. I have set it up so that if an assessment grade is the same as a child's target grade, then the box goes green. I would like to do the same, but with higher than predicted (blue) and also for those not achieving target grade (orange if just one sub-grade away, red in two sub-grades and purple if a full grade away. I have encountered some problems.

1. I used conditional formatting for the colour, but i can't see how I can apply this to all the boxes I want it to, rather than just the box I created it with.
2. The colours keep overwriting each other.
3. I am using a separate page to show the grade boundaries, and I can't seem to link them to the first page.

Year 7 template.xlsx

you should be able to setup
Based on a table that converts to a number and then use the number as a format
That way you can tell if its 1 or 2 grades below

Are the grades letters important
so 6C v 6B v 6A
do they also get counted

You could assign
8a 30
8b 29
8c 28

etc

then use a lookup to lookup the target value
then use the lookup to lookup the actual grade
take one away from the other
then you can assign a colour to
+3 +2 +1 0 -1 -2 -3 etc

would that work - or have I missed something