Smartsheet Behaviours

Julieh
Julieh ✭
edited 08/02/23 inFormulas and Functions

I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions?

Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in green. Does anyone know if this can be done?

image.png


· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would have to insert a new header field into the form that has that particular verbiage formatted the way you want then use the form field logic to display this header row if one of the incorrect answers is selected.

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

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
Hi @JacksonElla<\/a>,<\/p>

If \"Yesterday\" and \"Today\" are the only text values you end up with, then this formula would do what you're after:<\/p>

=IF([Last Updated]@row = \"Yesterday\", TODAY(-1), IF([Last Updated]@row = \"Today\", TODAY(), [Last Updated]@row))<\/p>

Ironically, typing Yesterday\/Today directly into a date column in Smartsheet will give the relevant date (as will last\/previous\/next <insert day>) but I don't know if the Jira integration would support that or you'd need to use a helper column with the formula as above.<\/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":108819,"type":"question","name":"Fill in a cell based on another cell's value?","excerpt":"Hello, I'm tracking participation of a program on my sheet as a \"participation %\" column. There is another column called \"participation status\" to make it easy for the managers to know if their employee is on track with participation or not. I'm trying to set up the participation status column so that if the participation…","snippet":"Hello, I'm tracking participation of a program on my sheet as a \"participation %\" column. There is another column called \"participation status\" to make it easy for the managers to…","categoryID":322,"dateInserted":"2023-08-10T20:29:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T21:09:33+00:00","insertUserID":164943,"insertUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164943,"lastUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":46,"score":null,"hot":3383402334,"url":"https:\/\/community.smartsheet.com\/discussion\/108819\/fill-in-a-cell-based-on-another-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108819\/fill-in-a-cell-based-on-another-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108819,"commentID":390127,"name":"Re: Fill in a cell based on another cell's value?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390127#Comment_390127","dateInserted":"2023-08-10T21:09:33+00:00","insertUserID":164943,"insertUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+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-08-10T21:09:38+00:00","dateAnswered":"2023-08-10T20:58:08+00:00","acceptedAnswers":[{"commentID":390125,"body":"

@ciera_wolinski<\/a> <\/p>

The formula below will get what you need. With Smartsheet, you have to convert % to decimal values in formulas. <\/p>

=IF([Participation %]@row = 0, \"Not Participating\", IF([Participation %]@row > 0.8, \"On Track\", IF(AND([Participation %]@row < 0.81, [Participation %]@row > 0), \"Needs Improvement\", \"Blank\")))<\/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":108817,"type":"question","name":"Hi, I am trying to do a SUMIFS, and I keep getting incorrect argument set","excerpt":"I have a column called Actual Charges I want to sum if the column SOW Reference has \"IPO PMO\" in it. I thought I was following the syntax and reduced to only one criteria. I do have other criteria, too. Please see formula below. I also wanted to sum the whole column Actual Charges, but it seems to only capture the column…","snippet":"I have a column called Actual Charges I want to sum if the column SOW Reference has \"IPO PMO\" in it. I thought I was following the syntax and reduced to only one criteria. I do…","categoryID":322,"dateInserted":"2023-08-10T19:41:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T20:11:11+00:00","insertUserID":136371,"insertUser":{"userID":136371,"name":"Mark Singleton","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Singleton","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T20:42:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T12:13:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":38,"score":null,"hot":3383396531,"url":"https:\/\/community.smartsheet.com\/discussion\/108817\/hi-i-am-trying-to-do-a-sumifs-and-i-keep-getting-incorrect-argument-set","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108817\/hi-i-am-trying-to-do-a-sumifs-and-i-keep-getting-incorrect-argument-set","format":"Rich","lastPost":{"discussionID":108817,"commentID":390119,"name":"Re: Hi, I am trying to do a SUMIFS, and I keep getting incorrect argument set","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390119#Comment_390119","dateInserted":"2023-08-10T20:11:11+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T12:13:02+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-08-11T09:14:41+00:00","dateAnswered":"2023-08-10T19:52:48+00:00","acceptedAnswers":[{"commentID":390115,"body":"

I assume you have a header or something similar in row 1, and that is why you are starting in row 2? If you designate rows, you must use it on the range to sum as well as the ranges to evaluate.<\/p>

=SUMIFS([Actual Charges]2:[Actual Charges]60, [SOW Reference]2:[SOW Reference]60, \"IPO PMO\")<\/p>

If you specify rows, those rows must also exist. If you want to evaluate all rows except for row 1, so specify [Actual Charges]2:[Actual Charges]2000, you will get an error if there are not 2000 rows in the sheet.<\/p>

You should also be able to add additional conditions\/rows to evaluate 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":[]}],"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":[]}">

Trending in Formulas and Functions

  • Convert the text "yesterday" to a date format.

    I downloaded some issues from Jira and I have a column from there titled "Last Updated". In that column it has the regular date format but also text that reads "Yesterday" or "Today". I figured I could use the TODAY() formula to convert the today dates, but how do I configure that to convert to yesterday? Is it TODAY(-1)?…

  • Fill in a cell based on another cell's value?

    Hello, I'm tracking participation of a program on my sheet as a "participation %" column. There is another column called "participation status" to make it easy for the managers to know if their employee is on track with participation or not. I'm trying to set up the participation status column so that if the participation…

  • Hi, I am trying to do a SUMIFS, and I keep getting incorrect argument set

    I have a column called Actual Charges I want to sum if the column SOW Reference has "IPO PMO" in it. I thought I was following the syntax and reduced to only one criteria. I do have other criteria, too. Please see formula below. I also wanted to sum the whole column Actual Charges, but it seems to only capture the column…