Flag based on multiple columns

Options
Badreldin Ahmed
edited 12/09/19 in Smartsheet Basics

Hi,

I have created a monthly scorecard smartsheet with formulas and conditional formatting to change the color of the cell based on certain criteria. Now I would like to add column with a flag if a KPI is red for 3 consecutive months. Can anyone share how can this be done?

Thanks in advance for your help! 

Comments

  • sean59916
    sean59916 ✭✭✭
    Options

    Hi Badreldin - 

    Can you share a screenshot so we can help you out with this? Essentially, you would use the conditions that you have defined in the conditional formatting.

    Sean

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Badreldin,

    It's not possible to base a formula on Conditional Formatting but it might be possible if you use an RYG column in combination/instead. Can the KPI value change from RED to something else again?

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Badreldin Ahmed
    Options

    Sean and Andree,

    Thank you both for responding. I am attached a screenshot and hope I will be able to better explain what I am trying to do:

    1. I have hidden columns that trigger the conditional formatting for each cell. As you can imagine, these columns contain simple IF statements that return (R, Y and G) based on the monthly target for the specific KPI.
    2. I would like the flag in RCA column to automatically turn red ONLY if the KPI result in that row is red for 3 consecutive month (example KPI 4).
    3. I will then use that flag to trigger automatic notification to the KPI owner.

    I hope this explains what I am trying to accomplish and thanks in advance for your help!

    Badreldin  

    Scorecard.JPG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help!

    Can the KPI RYG value for the months change from RED to something else again?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Badreldin Ahmed
    Options

    Thanks again Andree! The actual value for the same month will stay RED if the target is missed. The following month may be GREEN, YELLOW or RED again and so on..

    I hope I understood your question correctly and this gives you the answer.

    Badreldin

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    I'm always happy to help!

    Try something like this and continue the pattern for the rest of the months. You'll also need to change the column names to what you're using.

    =IF(AND(Jan@row = "Red"; Feb@row = "Red"; Mar@row = "Red"); 1;IF(AND(Feb@row = "Red"; Mar@row = "Red"; Apr@row = "Red"); 1;IF(AND(Mar@row = "Red"; Apr@row = "Red"; May@row = "Red"); 1;IF(AND(Apr@row = "Red"; May@row = "Red"; Jun@row = "Red"); 1)

    The same version but with the below changes for your and others convenience.

    =IF(AND(Jan@row = "Red", Feb@row = "Red", Mar@row = "Red"), 1,IF(AND(Feb@row = "Red", Mar@row = "Red", Apr@row = "Red"), 1,IF(AND(Mar@row = "Red", Apr@row = "Red", May@row = "Red"), 1,IF(AND(Apr@row = "Red", May@row = "Red", Jun@row = "Red"), 1)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Badreldin Ahmed
    Options

    Thank you Andree; it certainly worked! I was hoping for a shorter formula but this is perfect smiley

    Thanks again,

    Badreldin

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Excellent!

    Happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.