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
-
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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
What do you put in the helper column to indicate a formula?
-
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.
-
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.
-
Unfortunately there is not an ISFORMULA function, but that is definitely a great idea. Feel free to Submit a Product Enhancement Request when you are able.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
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.
-
@Paul Newcome 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.
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
-
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.
-
@Paul Newcome The 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.
-
Unfortunately flagging a cell just because it has a formula is a manual process.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!