I'm trying to create a data sheet that has student test scores. There are multiple sheet with test scores need to be conditionally formatted based of a range - ex: pass, fail, warning. However, the cut of scores vary from year to year and I need to update each formula manually.
To solve the problem of having to go back in and change each formula, I'd like to create a reusable conditional formatting rule in Google Sheets referencing score ranges from a separate sheet to avoid manual updates across multiple score sheets.
I know that I need to use INDIRECT to reference the other sheet, but I'm not sure what formula I should use to make the ranges work without having to list out every possible test score.
I made a simplified mockup of my problem here: Example Sheet
Is this possible to do in Google Sheets?
Add three conditional formatting rules for the range B2:B:
🟥 red:
Is between =indirect("Assessment Ranges!B3") and =indirect("Assessment Ranges!C3")
🟨 yellow:
Is between =indirect("Assessment Ranges!D3") and =indirect("Assessment Ranges!E3")
🟩 green:
Is between =indirect("Assessment Ranges!F3") and =indirect("Assessment Ranges!G3")