RAG Symbols Column Changed From A Project Status

CAS · 09/15/21

Hi,

I want to have a RAG column change the symbols dictated from the Project Status in a formula but can't work it out.

Example

Project status is 6 - IBM PO Inhand

RAG Symbol should turn to Green

I have a RAG column setup with the red, amber & green symbols.

Any help would be appreciated?

Lidiya S. · 09/15/21

Craig,

It is definitely possible to accomplish this! Could you share some more info on the use case? How many different project statuses should be red, how many green, how many amber?

In general I would use the following formula in the RAG column as a column formula:

=IF([Your Project Status column]@row = 6, "Green", IF([Your Project Status column]@row = 0, "Red", "Yellow"))

Or, if you are looking for a status on the entire sheet instead of a specific row:

=IF([Your Project Status column] = 6, "Green", IF([Your Project Status column] = 0, "Red", "Yellow"))

Pretty much say "If status is 6, then show Green, otherwise if status is 0, then show Red, otherwise show Yellow".

Just an FYI Smartsheet uses the nomenclature (Red,Yellow,Green) for the status ball symbols.

Hope this helps!

Lidiya Shutaya

lidiya@ddbconsultants.ca

CAS · 09/15/21

Hi Lidiya,

thank you for responding.

I have only three project status levels on a column called: Small Jobs Status

6 - IBM PO Inhand = Green

2 - Scoping = Red

3 - LSQ Submitted = Amber

The drop down options have the number prefix for some corresponding understanding to the contract. Not sure if that affects the formula working or not!

How would I write a complete formula for the above?

Kind regards

Craig

Lidiya S. · 09/15/21

Craig,

Thank you for the info! The status column being a dropdown should not matter.

Plugging what you have in should be pretty simple:

=IF([Small Job Status]@row = "6 - IBM PO Inhand", "Green", IF(([Small Job Status]@row = "2 - Scoping", "Red", "Yellow"))

Tip: If the above formula will be a column formula and not just a cell formula, make sure you use [Small Job Status]@row instead of [Small Job Status]1. If you use the specific cell number instead of "@row" you will get an error like the one below.

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

CAS · 09/16/21

Thanks Lidiya,

I still can't get this working.

I tried a formula like this below with just the numbers on a different column and its working fine & changing the RAG to the colors.

=IF([Status Level]@row = 6, "Green", IF([Status Level]@row = 2, "Yellow", IF([Status Level]@row = 3, "Red")))

But when I try it with the full worded cell data on my original column like this below it doesn't work for me?

=IF([Small Job Status]5 = 6 - IBM PO Inhand, "Green", IF([Small Job Status]5 = 2, "Yellow", IF([Small Job Status]5 = 3 - LSQ Submitted, "Red")))

kind regards

Craig

Andrée Starå · 09/16/21

Hi @Craig S

I hope you're well and safe!

Try something like this.

=
IF([Small Job Status]@row = "6 - IBM PO Inhand", "Green", 
IF([Small Job Status]@row = "2 - Scoping", "Yellow", 
IF([Small Job Status]@row = "3 - LSQ Submitted", "Red")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

CAS · 09/16/21

Thanks Andree!

That worked beautifully. Have a great weekend also.

kind regards

Craig

Andrée Starå · 09/16/21

@Craig S

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

RAG Symbols Column Changed From A Project Status

Answers

Help Article Resources

Categories