Return Red Amber Green

Mad

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

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

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.

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

@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å

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

Try something like this...

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

Mad

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

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.

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.

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

Mad

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

@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

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å

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

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

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