I'm a maths teacher and I'm storing my class data in an Excel spreadsheet. Each student occupies a row. Some columns relate to homework completion and they get a certain colour scale formatting. Other columns relate to test scores and they get a different colour scale formatting.
At the moment, when I add a new column for e.g. another homework exercise, I have to edit the rules manually, so the correct colour scale rule will apply to that particular column. This is quite fiddly and annoying.
I would like to make a single conditional formatting rule for the table that will apply a colour scale highlight to cells based on their values (e.g. red for 0, yellow for 0.5, green for 1), but ONLY if a cell at the TOP of that column matches a particular value.
To put it another way, I want my column headings to contain indicators for what kind of values go in the column, and I want Excel to apply different colour scale rules to each column based on the indicator at the top.
Is there a way to do this?
(My apologies if this is a duplicate question; I've searched but couldn't find something that connected conditional formatting based on a column header with a colour scale highlighting rule)