Return Red Amber Green
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
Best Answers
-
Try something like this...
=IF(AND([Column1]@row = "Yes", [Column2]@row = "Yes"), "Green", IF(AND([Column1]@row = "No", [Column2]@row = "No"), "Red", "Yellow")
-
Here is a link to the basics:
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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
Hi @Mad
I hope you're well and safe!
To add to previous excellent advice/answers.
Here's another excellent resource, Formula Foundations Webinars.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Try something like this...
=IF(AND([Column1]@row = "Yes", [Column2]@row = "Yes"), "Green", IF(AND([Column1]@row = "No", [Column2]@row = "No"), "Red", "Yellow")
-
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
-
Here is a link to the basics:
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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Haha oops, you beat me to it, Paul!! The formula Template from the Solution Center is a great idea.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Paul Newcome Thank you for the links I will definitely use them. I really appreciate your help thank you
-
@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.
-
Hi @Genevieve P. I shall take a look at that now thank you so much I'm really happy I posted a question now
-
Hi @Mad
I hope you're well and safe!
To add to previous excellent advice/answers.
Here's another excellent resource, Formula Foundations Webinars.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you @Andrée Starå I shall take a look at these shortly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!