How to make a calculation return blank

Esther Aller

I have a simple IF() calc that I thought would return blank if false, but when I go to make a color rule on if this column is blank it doesn't work. If I copy the output of this calculation is appears to be returning whitespace, but I can't use [field] is equal to {put a space in the text box} in the color rule.

=IF(AND([Status]@row = "Assigned",ISBLANK([Area Served]@row)),"Area Served" ,"")

How do I return a blank?

Paul Newcome

That should be returning a blank. What is the formula that you are using to reference this cell?

Esther Aller

I'm creating a conditional formatting rule in the Cards view that references this calc. If I choose "is blank" as the condition in the UI, then that rule is never applied. If "is not blank" then that rule applies to everything.

I do definitely have rows where the output looks blank. When I copy/paste the output into another program, I get a space.

Paul Newcome

Are you able to provide a screenshot of the formula in the sheet similar to the screenshot below?

Esther Aller

Here you can see the calculation (same one as my original post) in "Test" calculation. You can see the results in grid view using the "intake/backlog review" filter: Test only returns blank. You can then see how I set up my color rule to give cards with blank Test a purple band. Finally, the card view with no purple bands.

formatting results.jpg

calc results in grid view.jpg

color rule.jpg

calculation.jpg

Paul Newcome

Can you provide a screenshot of the "this format"?

Esther Aller

"This format" doesn't tell you anything new, but here is that screenshot

this format.jpg

It sounds like this is unexpected behavior, so I have submitted a support request. I'll update this thread with what I hear back from smartsheet support.

Paul Newcome

I was just double checking to make sure you had the "Task Bar" set and not the cell highlighting. Even knowing this, if I am not paying attention to what I am doing, I have mixed this up on more than one occasion.

I'll keep an eye out for your update from Support.

mstewart

I am also seeing this same problem. My formula is roughly as follows:

=IFERROR(formula, "")

I have a conditional formatting rule which colors a row red when the cell value that this formula is in "is not blank". But if an error occurs and the value_if_error is used, the row gets colored red even though my value_if_error is "".

Have there been any updates on this problem?

Thanks