Formula to flag cell, based on level decreased in another cell

Miss_Priss

Hello All,

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!

ericncarr

@Miss_Priss Does it absolutely need to be a formula? If I'm understanding your question properly, I think you could just make the Tier Demotion to a checkbox and use a workflow - When a row changes, when Partner Tier changes to any value, Change Cell Value for Tier Demotion to Checked. Then when you've taken note of the change or whatever, you manually uncheck the box, rinse/repeat.

Paul Newcome

In the sheet that collects the forms... Are the forms populating at the top or the bottom of the sheet? If at the bottom, would you be able to flip it so that new entries populate at the top, or is it required to be at the bottom of the sheet?

Miss_Priss

@ericncarr This was one of my ideas as well but it does leave a manual element to it. My goal was to make it as little to no touch as possible. My current workaround idea is an automation that captures the date the Partner Tier cell changes (in the column to the right of Tier Demotion, Demotion Date). When a date is captured, I'd manually confirm if the Tier was a promotion or a demotion. If it was a promotion I would remove the date. If it was a demotion I would manually flag the cell in the Tier Demotion column.

@Paul Newcome Currently the sheet is not setup in that way. Right now the sheet is sorted by LVL which is the numerical value of the Partner Tier. I understand the suggestion though. That could be an option.

Paul Newcome

If you have new forms populating at the top of the sheet then you can use an INDEX/COLLECT to pull in the most recent as well as the second most recent then compare the two.

Most recent:

=INDEX(COLLECT({LVL Column}, {Partner Name Column}, @cell = [Partner Name]@row), 1)

Second most recent:

=INDEX(COLLECT({LVL Column}, {Partner Name Column}, @cell = [Partner Name]@row), 2)

Comparing the two:

=IF(INDEX(COLLECT({LVL Column}, {Partner Name Column}, @cell = [Partner Name]@row), 1) < INDEX(COLLECT({LVL Column}, {Partner Name Column}, @cell = [Partner Name]@row), 2), "DECREASE")

ericncarr

@Paul Newcome that's a nice way to solve it! I didn't realize the source data was supplied via form so that works great.

@Miss_Priss One thing I'll add to all this though is that I wonder about the need for a form...is it necessary for your users to submit a form every week? Depending on what exactly they're doing, you could give them a dynamic view to a sheet with all the Partners where they can update whatever they need to about the Partner with a form to add new ones and some sort of checkbox/workflow to remove old ones if need be. I know my stakeholders suffer from form fatigue!

Then whenever a change is made to a row, it copies the row to a history log and that's what I'd do a formula against from this sheet. I don't think you can have copy rows go to the top....so you'd have to use a Max function to get the latest entry and then a Large function to get the second most recent in order to do the comparison. Another way to go about it depending on requirements, the index(collect()) will get you what you need right away though.