Status Change RYG for Pass or Fail
I have a sheet with 20 different [Pass Fail 1], [Pass Fail 2], [Pass Fail 3], etc.. columns that if any Fail I would like the status RYG to turn Red. Then if all Pass to be Green. I have never created a formula that has a range of columns I want it to validate as a whole. Any suggestions?
Best Answer
-
Jon Mark H ✭✭✭✭✭
If the columns are all next to each other you can do this in the RYG column:
=IF(CONTAINS("FAIL", [Pass Fail 1]@row: [Pass Fail 20]@row), "Red", "Green")
This will turn the RYG column ball Red if ANY of the Pass Fail columns have "FAIL" in them, otherwise it will turn green.
Let me know if that helps or you need any further help!
-Jon Mark
Answers
-
Jon Mark H ✭✭✭✭✭
If the columns are all next to each other you can do this in the RYG column:
=IF(CONTAINS("FAIL", [Pass Fail 1]@row: [Pass Fail 20]@row), "Red", "Green")
This will turn the RYG column ball Red if ANY of the Pass Fail columns have "FAIL" in them, otherwise it will turn green.
Let me know if that helps or you need any further help!
-Jon Mark
-
HardWork ✭
Hi Jon,
They weren't right next to each other. There were columns in between with other parameters. I gave it a shot and it worked beautifully!!! Thank you so much.
Help Article Resources
Categories
Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106897,"type":"question","name":"Delinquency Report","excerpt":"Hi SmartSheet Community, How do you pull a delinquency report showing month to month stagnation, in the same column across sheets? I'd like to send a monthly update request form for the \"assign to\" with their lists and pull only those item's whose %complete hasn't been updated in the last 30days. Is there a column specific…","categoryID":322,"dateInserted":"2023-06-26T15:35:15+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:23:29+00:00","insertUserID":162550,"insertUser":{"userID":162550,"name":"Avani","title":"Senior Manager, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Avani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:44:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":19,"score":null,"hot":3375590924,"url":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","format":"Rich","lastPost":{"discussionID":106897,"commentID":382339,"name":"Re: Delinquency Report","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382339#Comment_382339","dateInserted":"2023-06-26T16:23:29+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:27:10+00:00","dateAnswered":"2023-06-26T16:23:29+00:00","acceptedAnswers":[{"commentID":382339,"body":" You would need to use a Record A Date automation to grab the date the % Complete column was last updated.<\/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":[]}],"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":[]}">
