Welcome!

Flag based on multiple columns

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!

Find more posts tagged with

Comments

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:

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.
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).
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

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…