Hi,
I have a test result spreadsheet I'm trying to format.
The main table is on Spreadsheet 1.
Each row is a different test, the columns are the test dates.
Most tests have a different minimum and maximum pass mark range.
The tests that don't have min and max I'm just going to make the range 0 to 999.
The table setting out the minimum and maximum values is on Spreadsheet 2
I want to sort the formatting so that scores below the minimum are marked red, scores above are blue and scores in equal and between are green.
So far the only way I know to format it is with 4 rules per row, and if I add a test I have to create another 4 rules.
There are over 70 tests, so I'm wondering if there's a better way of creating the rules. We thought maybe based on a VLOOKUP formula, but we couldn't work it out.
Many thanks
