How to make a calculation return blank
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?
Answers
-
That should be returning a blank. What is the formula that you are using to reference this cell?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Are you able to provide a screenshot of the formula in the sheet similar to the screenshot below?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Can you provide a screenshot of the "this format"?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
"This format" doesn't tell you anything new, but here is that screenshot
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!