Return Red Amber Green

Mad · 05/19/22

Hi All,

I wish to return a single rag status from two columns. The two Columns will display "Yes" or No individually.

If both columns are "Yes" I wish to return Green

If one column is "Yes" and the other "No (or vice versa) I wish to return Amber

If both columns are "No" I wish to return Red

I am not sure which function I should be using (IF, IF AND) and how to write the syntax

I appreciate any help as I am a novice and thank you in advance

Paul Newcome · 05/19/22

Try something like this...

=IF(AND([Column1]@row = "Yes", [Column2]@row = "Yes"), "Green", IF(AND([Column1]@row = "No", [Column2]@row = "No"), "Red", "Yellow")

Paul Newcome · 05/19/22

Here is a link to the basics:

https://help.smartsheet.com/functions

If you go into the solution center (click the + in the right hand toolbar) and search for "formulas", you should be able to find a template you can grab that has all of the functions listed out as well as examples actually within a sheet so that you can play with them a bit. If you mess the sheet up too bad, just download a new template and you're good to go.

I also recommend this link as well if you are going to be working with more formulas:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Genevieve P. · 05/19/22

Hi @Mad

I'm sure Paul has some good suggestions of resources for you, but I wanted to jump in to let you know it looks like your account has access to the eLearning courses in the Smartsheet University.

There's a course specifically on Intermediate Formulas that you may find helpful.

There's also an example of how to build a nested IF statement like this in the Advanced Formula webinar, here.

Cheers,

Genevieve

Paul Newcome · 05/19/22

@Genevieve P. I always forget about the SSU as an option. I guess I should take another look through there and see if there is anything new.

Andrée Starå · 05/19/22

Hi @Mad

I hope you're well and safe!

To add to previous excellent advice/answers.

Here's another excellent resource, Formula Foundations Webinars.

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations

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!

Paul Newcome · 05/19/22

Try something like this...

=IF(AND([Column1]@row = "Yes", [Column2]@row = "Yes"), "Green", IF(AND([Column1]@row = "No", [Column2]@row = "No"), "Red", "Yellow")

Mad · 05/19/22

https://community.smartsheet.com/discussion/comment/330272#Comment_330272

Works a treat thank you so much! Could you recommend a source for me to use so as I can learn how to write syntax correctly? I have a very basic understanding at the moment

Paul Newcome · 05/19/22

Here is a link to the basics:

https://help.smartsheet.com/functions

If you go into the solution center (click the + in the right hand toolbar) and search for "formulas", you should be able to find a template you can grab that has all of the functions listed out as well as examples actually within a sheet so that you can play with them a bit. If you mess the sheet up too bad, just download a new template and you're good to go.

I also recommend this link as well if you are going to be working with more formulas:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Genevieve P. · 05/19/22

Hi @Mad

I'm sure Paul has some good suggestions of resources for you, but I wanted to jump in to let you know it looks like your account has access to the eLearning courses in the Smartsheet University.

There's a course specifically on Intermediate Formulas that you may find helpful.

There's also an example of how to build a nested IF statement like this in the Advanced Formula webinar, here.

Cheers,

Genevieve

Genevieve P. · 05/19/22

Haha oops, you beat me to it, Paul!! The formula Template from the Solution Center is a great idea.

Mad · 05/19/22

https://community.smartsheet.com/discussion/comment/330277#Comment_330277

@Paul Newcome Thank you for the links I will definitely use them. I really appreciate your help thank you

Paul Newcome · 05/19/22

@Genevieve P. I always forget about the SSU as an option. I guess I should take another look through there and see if there is anything new.

Mad · 05/19/22

https://community.smartsheet.com/discussion/comment/330278#Comment_330278

Hi @Genevieve P. I shall take a look at that now thank you so much I'm really happy I posted a question now

Andrée Starå · 05/19/22

Hi @Mad

I hope you're well and safe!

To add to previous excellent advice/answers.

Here's another excellent resource, Formula Foundations Webinars.

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations

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!

Mad · 05/20/22

https://community.smartsheet.com/discussion/comment/330300#Comment_330300

Thank you @Andrée Starå I shall take a look at these shortly

Return Red Amber Green

Best Answers

Answers

Help Article Resources

Categories