Flag based on multiple columns

Badreldin Ahmed

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! 

sean59916

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å

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

Badreldin Ahmed

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  

Andrée Starå

Happy to help!

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

Best,

Andrée

Badreldin Ahmed

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å

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

Badreldin Ahmed

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

Thanks again,

Badreldin

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée