Red, Yellow, Green, Blue

HardWork
HardWork ✭
edited 01/17/22 inFormulas and Functions

=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", ISBLANK([CEO Approval to Close]@row = " ", "Blue", ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))

Stage 1 - Request to Close

Approved - Red

Declined - Yellow

Blank - Blue

Stage 2 - Request to Open

批准- Green

Declined - Red

Blank - Blue

But my formula is giving me an error message. Both stages are on the same row. Stage 2 trumps Stage 1. What am I doing wrong?

· Share on Facebook Share on Twitter

Best Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    Gotcha. I missed that you had ISBLANK and = " ". You don't need both because ISBLANK will return true if it is blank or false if it is not. This formula should resolve all of your scenarios.

    =IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row), "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row), "Blue"))))))

    · Share on Facebook Share on Twitter
  • HardWork
    HardWork ✭
    Answer ✓

    You are the best!!! That did the trick, thank you so much!

    · Share on Facebook Share on Twitter

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    The formula to this point makes sense and I think would resolve:

    =IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red"

    However, after that you move into ISBLANK, which will either return True or False. I think what you want to do is put an IF(ISBLANK( and follow that scheme through the end of your formula. That should make it resolve without error. So something like:

    =IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row = " ", "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))))

    · Share on Facebook Share on Twitter
  • HardWork
    HardWork ✭

    Thank you so much David for helping.

    I got an "Incorrect Argument Set" error message with your:

    =IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row = " ", "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))))


    Any other thoughts?

    · Share on Facebook Share on Twitter
  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    Gotcha. I missed that you had ISBLANK and = " ". You don't need both because ISBLANK will return true if it is blank or false if it is not. This formula should resolve all of your scenarios.

    =IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row), "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row), "Blue"))))))

    · Share on Facebook Share on Twitter
  • HardWork
    HardWork ✭
    Answer ✓

    You are the best!!! That did the trick, thank you so much!

    · Share on Facebook Share on Twitter
  • David Tutwiler
    David Tutwiler Overachievers Alumni

    That's awesome. Glad it's working.

    · Share on Facebook Share on Twitter
  • Cimafranca
    Cimafranca ✭

    @David Tutwiler

    I have an even more basic question regarding Green, Yellow, and Red. I thought typing any of these color descriptors in parentheses would yield the corresponding color icon for IF functions. While I can get my IF function to behave properly, the formula is returning the actual word Green, Yellow, or Red.How do I get this IF function to return the appropriately colored dot? And theColumn Propertyneeds to be set toText/Number, correct?

    =IF([email protected]= "Completed", "Green", IF([Due Date]@row < TODAY(7), "Yellow", IF([Due Date]@row > TODAY(0), "Red")))

    · Share on Facebook Share on Twitter
  • David Tutwiler
    David Tutwiler Overachievers Alumni

    @CimafrancaThe good news is that the formula looks correct. I believe your issue is with the column property. For this to work you have to set the column up to use Symbols, and then select the Red/Green/Yellow bubbles as your symbol choice.

    Otherwise, you are just returning the words Red/Green/Yellow and Smartsheet will put those words in the cell instead. Setting the column type to Symbols lets Smartsheet know that you want to convert the text to the appropriate symbol.

    · Share on Facebook Share on Twitter
  • Cimafranca
    Cimafranca ✭

    @David TutwilerYes - your recommendation fixed it! From the tutorials, I was under the impression that formulas only worked inText/Numberformat. I now see the appropriate color dot. Thank you so much for your help!

    · Share on Facebook Share on Twitter
  • David Tutwiler
    David Tutwiler Overachievers Alumni

    No problem, glad that got it going.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Nevermind, I resolved it!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]},{"discussionID":107817,"type":"question","name":"How to get the formulas to correctly count the completed tasks?","excerpt":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get it to only count when all subtask from its proper row have been completed. There are 4 tasks and over 60 subtasks. When the subtasks are…","snippet":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get…","categoryID":322,"dateInserted":"2023-07-19T19:04:08+00:00","dateUpdated":"2023-07-19T19:16:18+00:00","dateLastComment":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163690,"lastUserID":163690,"lastUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":41,"score":null,"hot":3379592666,"url":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107817,"commentID":386077,"name":"Re: How to get the formulas to correctly count the completed tasks?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386077#Comment_386077","dateInserted":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2N6H1QH5FWDV\/screenshot.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"ScreenShot.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T19:24:31+00:00","dateAnswered":"2023-07-19T19:19:24+00:00","acceptedAnswers":[{"commentID":386066,"body":"

@Ruvalcaba12<\/a> try his<\/p>

Create a helper column called \"Helper\" and put this column formula<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = 0, \"//www.santa-greenland.com/community/discussion/comment/\", IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), 1, 0))<\/p>

Then in your top level put this formula<\/p>

=COUNTIFS(CHILDREN(Helper@row), 1) + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/p>"},{"commentID":386074,"body":"

@Ruvalcaba12<\/a> Maybe if you change the text of your second tier children to this or something similar<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), \"Complete\", COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\")<\/p>

Then your top tier would be <\/p>

=COUNTIFS(CHILDREN(), \"Complete\") + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/p>"},{"commentID":386075,"body":"

You could keep the number but you have to change something to distinguish. Second Tier:<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Complete\", COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\")<\/p>

Top tier:<\/p>

=COUNTIFS(CHILDREN(), CONTAINS(\"Complete\", @cell)) + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107785,"type":"question","name":"Formula Needed","excerpt":"Hi Smartsheet Team, I am looking for help with a formula: I have a column \"Schedule Health\" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns \"Status\", \"Start Date\", \"End Date\". Ball is GREEN if \"Start Date\" is 7 days from today (ie project has not started yet) Ball is…","snippet":"Hi Smartsheet Team, I am looking for help with a formula: I have a column \"Schedule Health\" that has RYG Balls I am wanting this to change color based on certain criteria from 3…","categoryID":322,"dateInserted":"2023-07-19T12:29:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T00:06:46+00:00","insertUserID":163658,"insertUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163658,"lastUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3379583194,"url":"https:\/\/community.smartsheet.com\/discussion\/107785\/formula-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107785\/formula-needed","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107785,"commentID":386098,"name":"Re: Formula Needed","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386098#Comment_386098","dateInserted":"2023-07-20T00:06:46+00:00","insertUserID":163658,"insertUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T15:43:55+00:00","dateAnswered":"2023-07-19T13:51:54+00:00","acceptedAnswers":[{"commentID":385913,"body":"

Hey @Rod Sanderson<\/a> ,<\/p>

=IF(OR(AND(Status@row <> \"Complete\", [End Date]@row <= TODAY()), AND(Status@row = \"in progress\", [End Date]@row >= TODAY()), AND(Status@row = \"in progress\", [Start Date]@row <= TODAY())), \"Red\", 0)<\/p>

I think this should work for the red.<\/p>

Let me know if this is the direction you are looking for and I can write the rest of the colors as well.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

趋势在公式和函数

  • COUNTIF Issue on Sheet Summary

    I am trying to see how many times Q1, Q2, Q3, and Q4 appear in my "Quarter" column on my sheet within my sheet summary. I am using the formula =COUNTIF([Quarter:Quarter], "Q1") to try to get just Q1 and it is not working. Any suggestions on why that is not working?

  • How to get the formulas to correctly count the completed tasks?

    The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done" I have been trying to get it to only count when all subtask from its proper row have been completed. There are 4 tasks and over 60 subtasks. When the subtasks are…

  • 公式需要

    Hi Smartsheet Team, I am looking for help with a formula: I have a column "Schedule Health" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns "Status", "Start Date", "End Date". Ball is GREEN if "Start Date" is 7 days from today (ie project has not started yet) Ball is…