FORMULA: Conditionally Format Cells with Errors

This is unlikely possible with Smartsheet but I'd LOVE to be proven wrong :)

I'm trying to Highlight Cells in the row that have errors. There might be one to twenty columns in that row with errors at any given time.

Reason for it: One broken formula (or cell with bad data) can break several other formulas in that row. And if you have a lot of columns in the sheet or if you're developing the sheet it could be difficult to identify the problem if you don't fix it when it right when it happens. So the goal is to quickly identify when a change breaks one or more formulas in that row.

In the below example, Columns "Badformula1", "Badformula2", and "Badformula3" all have some sort of formula error and would be highlighted if there was a problem. This could be three different conditional formatting rules, that's OK. (The image is the wishful thinking image of what I want it to look like, not a working one)

{B86744B7-036B-4BCA-AA6A-195F51171D68}.png

The work-around I've found only works in a couple situations and not all of them, which is to set a conditional formatting rule for each column based on if the cell breaks the normal data type in that form.

Examples:

BadFormula1 = Always a Number. So the condition would be, IF BadFormula1 is not a number,
Badformula2 = Always a letter, so the condition would be, "IF BadFormula2 is not 1 or 2 or 3 or 4
In BadFormula3 you'll see that the value is not predictable so there's no "workaround" (That I have found)

So my question to the community applies to cells with unpredictable values.

I tried:

Using a helper column and using IF(ISERROR like this: =IF(ISERROR([BadFormula1]@row), "Error", "No Error"). My hope was to conditionally format based on the value of "Error".
1. OUTCOME: #BLOCKED error message in that cell. Not helpful.
I also tried "IFERROR" in both the cell itself and a helper column. same thing happens.
1. OUTCOME: Same as #1
Googling it
1. Same same solution was given but it doesn't work because it's referencing a cell with an error.
Using AI,
1. Same same solution was given but it doesn't work because it's referencing a cell with an error.

Does anyone here have a solution that works?

Alternatively, if there's a way to use a formula to check all rows in the sheet for errors and display SPECIFIC TEXT like "Error found", that would be OK too. (See yellow highlighted cell in image above)

Find more posts tagged with

Formulas

Conditional Formatting

error checking

Accepted answers

jessica.smith

Hey Neil, I think this is because I cheated and just used "#UNPARSEABLE" etc text in the fields I was referencing. Apologies, I didn't realize Smartsheet would treat my text different than a formula, but I should have.

Smartsheet will insert #BLOCKED when at least one of the cells referenced by the formula has an error, so the results you are seeing make sense.

An alternative idea is to use an automation to detect an error and then check the corresponding error checkbox, instead of using a formula. This time I tested with real error formulas and it seems to work.

All comments

jessica.smith

Hi Neil,

What if you add an error detection checkbox colum for each column you want to monitor with a formula that looks something like:

=IF(CONTAINS("#", [Column2]@row), 1, 0)

Then create a single conditional formatting rule for each Error Detection column to highlight just the column that flagged the error on that row.

NeilKY

@jessica.smith I don't know what's wrong but when I try your formula, it doesn't work like yours. When there's an error in the cell I'm referencing, it gives me an error in the cell with the formula.

IF(CONTAINS("#", [BadFormula1]1), 1, 0)

{57518825-E9C1-4010-804A-88E00D986DB2}.png

2. IF(CONTAINS("#", [BadFormula2]1), 1, 0)

{EEEF8E8B-9445-4AC0-A1C8-C1D9469A220E}.png

jessica.smith

Smartsheet will insert #BLOCKED when at least one of the cells referenced by the formula has an error, so the results you are seeing make sense.

NeilKY

https://community.smartsheet.com/discussion/comment/471923#Comment_471923

@jessica.smith It's great to know that automations can recognize "#" symbols whereas formulas do not, when it's an error. Very useful! Thank you.

However, I think creating a helper column for every column to check is just not feasible.

Thankfully, due to your idea I was able to create a solution that uses your tip!

—

SOLUTION BELOW:

The result of the following solution is that the "Automation Error Check" box will be checked if any error is found in any column between the "First column" and "Last Column".

{8BCA3101-98AD-4A1F-A30A-1108FB0C4A43}.png

Step 1: Create "First Column" and a "Last Column" columns—These columns encompass the columns you want to check for errors. Leave the values in both columns blank (Blue columns in image).

Step 2: Create a "Checkbox" Col to the left of "First Column" with the following formula:

=IF(ISERROR([FIRST COLUMN]@row:[LAST COLUMN]@row), 1)

Step 3: Add another "Checkbox" Column. It can reside anywhere outside of the FIRST COLUMN & LAST COLUMN Range.

Step 4: Create an automation like this that references the column created in step 3:

{797ED9D0-3E25-4963-B245-AD4C4A2277B4}.png

The reason you need both conditions is because sometimes the formula will find an error and flag itself and sometimes it will error out. To identify formulas, as you stated, you have to use the automation to look for the # symbol. So this utilizes both methods.

You'll also want to create another automation to UNCHECK the "Automation Error Check" box if the formula column changes to 0.

{1D72125F-BEE0-4077-BE13-A6F1F281EA66}.png

—

Thanks for the idea!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.