Formula to flag cell, based on level decreased in another cell
After running into a dead end, I am in need of some guidance. Currently I am trying to find a formula solution that will flag a cell, if a Partner tier/ level decreases in another cell. The sheet I am using is linked to another sheet that tracks the Partner's current tier/ level, which is updated weekly. At times a Partner will be demoted a tier/ level & currently the only way this is indicated is the font of the tier name is turned red. I don't think there's an option for Smartsheet to flag based on the font color change in a cell, so instead I am hoping to find a solution by tracking the decrease in the Partner tier/ level.
Below is a baseline of the associated columns:
Tier Demotion: This is the column for the formula. A cell will be flagged if the tier/ level decreases.
Partner Tier: This is the column that indicates the current tier/ level, font is red if there is a decrease
LVL (Level): This is a helper column that indicates numerical value based on Partner Tier up to Level 5 (i.e. Registered = Level 1, Select = Level 2, etc.)
The simplest route might be a formula that tracks a decrease of the Partner Level which is a numerical value, rather than trying to track the Partner Tier names. Open to any suggestions!
Help Article Resources
Check out the Formula Handbook template!