Conditional Formatting text to numbers

Hi guys

I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levels

• Statement
• School Action Plus
• School Action
• Monitor

In year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.

I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula

What I'm trying to achieve
ExcelHelp.jpg

My Options sheet
Excelhelp2.jpg

If someone could point me in the right direction, it would be much appreciated.. I have the excel file which I can upload if need be.

Many thanks

Chris

Hi.

Apologies for the late response, I wasn't in work yesterday.

The file is attached, thanks for any assistance

Chris

SEN Progression Grid (Help).xlsx

Hi guys..

I'm still kinda struggling with this one if you could help?

Cheers

Chris