Use Conditional formatting to mark cells that contain formulas

RPIArchShop · 02/27/20

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

Andrée Starå · 02/28/20

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!

Lisa · 03/12/20

What do you put in the helper column to indicate a formula?

Paul Newcome · 03/12/20

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 · 03/12/20

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 · 03/12/20

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.

Andrée Starå · 03/15/20

@Lisa

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Marina · 07/11/22

@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 · 07/11/22

Marina

Jul 11, 2022

@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.

Marina · 07/11/22

@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

Paul Newcome · 07/11/22

Marina

Jul 11, 2022

@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.
https://us.v-cdn.net/6031209/uploads/U30P2REJZCUD/image.png

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
https://us.v-cdn.net/6031209/uploads/A6FCG5XFE58M/image.png

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 · 07/11/22

@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.

Paul Newcome · 07/11/22

Unfortunately flagging a cell just because it has a formula is a manual process.

Formulas and Functions

Formulas and Functions

Use Conditional formatting to mark cells that contain formulas

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula Help

Unparseable Error

Formula to return the non-blank cell