Use Conditional formatting to mark cells that contain formulas
Good Afternoon Smartsheet wizards,
While I am relatively new to Smartsheet and have purchased some of the learning, the Conditional Formatting is entertaining. I'd like to prove that certain cells have formulas, or not.
Thank you in advance for your help.
Cheers,
Bill
Answers
-
Andrée Starå ✭✭✭✭✭✭Hi Bill,
You’d have to use so-called helper columns for there isn’t a way in Conditional Formatting to check if it’s a formula or not.
Would that work?
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. 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.
-
Lisa ✭✭What do you put in the helper column to indicate a formula?
-
Paul Newcome ✭✭✭✭✭✭The helper column(s) would have to be manual entry to designate that there is a formula. There are a few different ways you could set it up though. One option would be to enter the column name(s) in the helper column of those that have formulas in them. Then you would base your conditional formatting rule(s) on what text is contained in the helper column rows.
-
Lisa ✭✭
That's a good option. I was hoping the helper column could also help me identify when someone accidentally erases a formula which happens too often around here. I don't see any functions like ISFORMULA (Excel has this), though.
-
Paul Newcome ✭✭✭✭✭✭
Unfortunately there is not an ISFORMULA function, but that is definitely a great idea. Feel free toSubmit a Product Enhancement Requestwhen you are able.
-
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.
-
Marina ✭
@Andrée StaråWhat formula can I put under helper column if I need to check if parent row has =SUM(CHILDREN) formula or not since when I copy the records form different sheet via automation, the formula =SUM(CHILDREN) is disappearing. I would like to use conditional formatting later to highlight the cell if the PARENT cell contains =SUM(CHILDREN) formula or not.
-
Paul Newcome ✭✭✭✭✭✭
You would need to use conditional formatting to highlight the row on the source sheet. You would need to set up a helper column with a formula to indicate which rows are parent rows.
-
Marina ✭
@Paul NewcomeI already have a helper column that identify PARENT and CHILDREN rows, I need to highlight only cells where =SUM(CHILDREN) formula is missing after coping the records from another sheet. This is what is set up on original sheet.
This is what I am getting after the records are copied (in those fields I get value only), so I want to highlight the cells only where the formula is missing
-
Paul Newcome ✭✭✭✭✭✭
Are you re-inserting the formula into those rows? If not, it would be missing on every row because the copy and move row automations both only pull static data. If you are, then you would have to manually flag each row after re-insertion to show that you have already done those as there is currently no function or other way to indicate that a formula exists in a cell.
-
Marina ✭
@Paul NewcomeThe formulas are already set up on the template file, but when they are copied to master file they are disappear. I would need to add them manually on master file after the records are copied. If I could highlight cells on master file where the formula are missing, it would give me less chances for mistakes (once I update the formula, it would un-highlight the cell). If nothing can be done, I will just pay more attention.
-
Paul Newcome ✭✭✭✭✭✭
Unfortunately flagging a cell just because it has a formula is a manual process.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-27T02:16:35+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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-08-26T17:06:33+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/U30P2REJZCUD\/image.png<\/a><\/p> <\/p>This is what I am getting after the records are copied (in those fields I get value only), so I want to highlight the cells only where the formula is missing <\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/A6FCG5XFE58M\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" I already have a helper column that identify PARENT and CHILDREN rows, I need to highlight only cells where =SUM(CHILDREN) formula is missing after coping the records from another sheet. This is what is set up on original sheet. \\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/U30P2REJZCUD\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/U30P2REJZCUD\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\" \\nThis is what I am getting after the records are copied (in those fields I get value only), so I want to highlight the cells only where the formula is missing \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/A6FCG5XFE58M\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/A6FCG5XFE58M\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\"}]","format":"rich","dateInserted":"2022-07-11T12:32:00+00:00","insertUser":{"userID":126848,"name":"Marina","url":"https:\/\/community.smartsheet.com\/profile\/Marina","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-08T17:50:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/335739#Comment_335739","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/335739#Comment_335739](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/U30P2REJZCUD\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/A6FCG5XFE58M\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" I already have a helper column that identify PARENT and CHILDREN rows, I need to highlight only cells where =SUM(CHILDREN) formula is missing after coping the records from another sheet. This is what is set up on original sheet. \\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/U30P2REJZCUD\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/U30P2REJZCUD\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\" \\nThis is what I am getting after the records are copied (in those fields I get value only), so I want to highlight the cells only where the formula is missing \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/A6FCG5XFE58M\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/A6FCG5XFE58M\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\"}]","format":"rich","dateInserted":"2022-07-11T12:32:00+00:00","insertUser":{"userID":126848,"name":"Marina","url":"https:\/\/community.smartsheet.com\/profile\/Marina","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-08T17:50:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/335739#Comment_335739","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/A6FCG5XFE58M\/image.png\"){kind=link}