Automation
Hello, I am looking for a way to condition a symbol column to updated from a text column. Any tips?
Best Answer
-
Stefan ✭✭✭✭✭✭
Hi@AGM,
ahh, the screenshot makes it easier to help :-)
What you need is a nested IF formula that caters for your status.
As you have only 4 colour balls but 5 options you may need to switch to other symbols providing more than 4 icons.
For easier reading let's say: refining = 1 / defined = 2 / in-progress = 3 / completed = 4 / accepted = 5.
=IF([Schedule State]@row = "1", "Red", IF([Schedule State]@row = "2", "Yellow", IF([Schedule State]@row = "3", "Green", IF([Schedule State]@row = "4", "Blue", "Grey"))))
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers
-
Andrée Starå ✭✭✭✭✭✭Hi@AGM
I hope you're well and safe!
Try something like this.
=IF([email protected]= "Yes", "Green")
Did that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
AGM ✭
Thanks@Andrée Starå!
What if the reference sheet is the same?
-
Andrée Starå ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
AGM ✭
I am trying to add a formula that will update my "Current Status" with the associated Color Ball based on the "Schedule Status" column.
The formula that you provided is working. However, it does NOT account for multiple options. As I have four different color balls and five "Schedule State" options to coordinate.
Current Formula that is working for single option: =IF([Schedule State]@row = "Accepted", "Blue")
-
Stefan ✭✭✭✭✭✭
Hi@AGM,
ahh, the screenshot makes it easier to help :-)
What you need is a nested IF formula that caters for your status.
As you have only 4 colour balls but 5 options you may need to switch to other symbols providing more than 4 icons.
For easier reading let's say: refining = 1 / defined = 2 / in-progress = 3 / completed = 4 / accepted = 5.
=IF([Schedule State]@row = "1", "Red", IF([Schedule State]@row = "2", "Yellow", IF([Schedule State]@row = "3", "Green", IF([Schedule State]@row = "4", "Blue", "Grey"))))
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
AGM ✭
Thanks you so much for your assistance.️
Help Article Resources
Categories
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/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":[]},{"discussionID":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T20:04:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T20:04:25+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:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":" 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"}]}],"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":[]}">
