Conditional Formatting Based on Number of Characters

Ben B.
Ben B. ✭✭
inSmartsheet Basics

Is it possible to apply Conditional Formatting to a cell based on the number of characters in the cell? I can think of how to do it using a helper column, but I'm curious if there's a way without adding an additional column.

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey@Ben B.

    Conditional Formatting does not currently have way of evaluating a cell to see how many characters are in it; I would also suggest using a helper column witha LEN formulato count the characters, then base the formatting off of that column instead.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter

Answers

Categories

@saranj<\/a> You could build in a formula in your new sheet that identifies if it is a child\/parent and if child to \"Move a row\" to a trash sheet. You can use a flag or text column<\/p>

=IF(NOT(ISBLANK(PARENT(Primary@row))), \"Child\", \"Parent\") For a text Column<\/p>

=IF(NOT(ISBLANK(PARENT(Primary@row))), 1, 0) For a flag Column<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111867,"type":"question","name":"Conditional Formatting Based on Number of Characters","excerpt":"Is it possible to apply Conditional Formatting to a cell based on the number of characters in the cell? I can think of how to do it using a helper column, but I'm curious if there's a way without adding an additional column.","snippet":"Is it possible to apply Conditional Formatting to a cell based on the number of characters in the cell? I can think of how to do it using a helper column, but I'm curious if…","categoryID":321,"dateInserted":"2023-10-18T20:19:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T14:32:27+00:00","insertUserID":167431,"insertUser":{"userID":167431,"name":"Ben B.","title":"Business Process Manager","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T19:01:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":167431,"lastUser":{"userID":167431,"name":"Ben B.","title":"Business Process Manager","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T19:01:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3395473929,"url":"https:\/\/community.smartsheet.com\/discussion\/111867\/conditional-formatting-based-on-number-of-characters","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111867\/conditional-formatting-based-on-number-of-characters","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":111867,"commentID":400953,"name":"Re: Conditional Formatting Based on Number of Characters","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400953#Comment_400953","dateInserted":"2023-10-20T14:32:27+00:00","insertUserID":167431,"insertUser":{"userID":167431,"name":"Ben B.","title":"Business Process Manager","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T19:01:10+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T14:31:38+00:00","dateAnswered":"2023-10-20T13:07:08+00:00","acceptedAnswers":[{"commentID":400902,"body":"

Hey @Ben B.<\/a> <\/p>

Conditional Formatting does not currently have way of evaluating a cell to see how many characters are in it; I would also suggest using a helper column with a LEN formula<\/a> to count the characters, then base the formatting off of that column instead. <\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":111831,"type":"question","name":"Primary column from multiple sheets","excerpt":"Hi, Is there a limitation on the number of primary column that can be displayed in the calendar view for a report with multiple sheets as sources? It seems it can only show 3.","snippet":"Hi, Is there a limitation on the number of primary column that can be displayed in the calendar view for a report with multiple sheets as sources? It seems it can only show 3.","categoryID":321,"dateInserted":"2023-10-18T14:57:29+00:00","dateUpdated":"2023-10-18T14:58:55+00:00","dateLastComment":"2023-10-20T14:15:56+00:00","insertUserID":168698,"insertUser":{"userID":168698,"name":"Daniel_P","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Daniel_P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7ENXO5NmQLw!hLdVIUco7Lo!wO_0LZPFRbV","dateLastActive":"2023-10-20T14:10:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":168698,"lastUserID":168698,"lastUser":{"userID":168698,"name":"Daniel_P","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Daniel_P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7ENXO5NmQLw!hLdVIUco7Lo!wO_0LZPFRbV","dateLastActive":"2023-10-20T14:10:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":38,"score":null,"hot":3395454805,"url":"https:\/\/community.smartsheet.com\/discussion\/111831\/primary-column-from-multiple-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111831\/primary-column-from-multiple-sheets","format":"Rich","lastPost":{"discussionID":111831,"commentID":400946,"name":"Re: Primary column from multiple sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400946#Comment_400946","dateInserted":"2023-10-20T14:15:56+00:00","insertUserID":168698,"insertUser":{"userID":168698,"name":"Daniel_P","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Daniel_P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7ENXO5NmQLw!hLdVIUco7Lo!wO_0LZPFRbV","dateLastActive":"2023-10-20T14:10:04+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/1F8REXIBQ582\/report-calendar-view-png.png"},"alt":"Report-calendar view.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T14:11:29+00:00","dateAnswered":"2023-10-20T13:33:37+00:00","acceptedAnswers":[{"commentID":400911,"body":"

Hi @Daniel_P<\/a> <\/p>

In a Report, the Report brings together the Primary Column across all sheets selected. I've color-coded my rows so you can see when they come from different sheets.<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

As long as the title<\/strong> of your Date Columns is the same in your source sheets, the Primary Column data should show on the corresponding date in the Calendar View. <\/p>

The different colours represent different sheets:<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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=321&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":5184,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Creating Automation to Log Specific Field Changes from One Sheet to Another?

    Hello, I have a large multi-year project tracker. We have a manual select Risk column, where users will select the field should they anticipate a risk for the deliverable in question. I want to create an automation where when "Risk" is selected an automation is created to copy the entire row to a new sheet where the risk…

  • Conditional Formatting Based on Number of Characters

    Is it possible to apply Conditional Formatting to a cell based on the number of characters in the cell? I can think of how to do it using a helper column, but I'm curious if there's a way without adding an additional column.

  • Primary column from multiple sheets

    Hi, Is there a limitation on the number of primary column that can be displayed in the calendar view for a report with multiple sheets as sources? It seems it can only show 3.

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

 Watch video