Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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)

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:

  1. BadFormula1 = Always a Number. So the condition would be, IF BadFormula1 is not a number,
  2. Badformula2 = Always a letter, so the condition would be, "IF BadFormula2 is not 1 or 2 or 3 or 4
  3. 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:

  1. 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.
  2. I also tried "IFERROR" in both the cell itself and a helper column. same thing happens.
    1. OUTCOME: Same as #1
  3. Googling it
    1. Same same solution was given but it doesn't work because it's referencing a cell with an error.
  4. 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)

-Neil

Answers

  • ✭✭✭✭✭✭

    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.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

  • ✭✭✭✭✭

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

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

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

    -Neil

  • ✭✭✭✭✭✭

    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.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions