Return Red Amber Green

Question

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 · Accepted Answer

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 · Accepted Answer

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. · Accepted Answer

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 · Accepted Answer

@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å · Accepted Answer

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!