Formula creates a String - Cell no longer blank/empty conditional formating

02/12/18 Edited 12/09/19

Hello,

 

I have a particular problem, I have a series of cells that have formulas to detect the checked values of a series of Check Boxes, creating conditional formatting and inserting 'N/A' in certain cells:

=IF([Assemblage Mecanique]8 = 1, "N/A", IF(Coupe8 = 1, "N/A", ""))

 after this check the cell retains a sting of nothing = ''''

 I have additional conditional formatting related to a check box to be performed on this same series of cells (show empty cells) - however this no longer works, as the conditional formatting condition of 'is Blank'  no longer functions.

 

I work around was inserting a ''-'' hyphen in the first formula, and this works for the second condition with 'Contains = -' 

=IF([Assemblage Mecanique]8 = 1, "N/A", IF(Coupe8 = 1, "N/A", "-"))

However, I don't what all the hyphens everywhere on my sheet.

the second screen capture shows a circled cell which should be red, as it is empty, however data is now "" from first formula.

Any other way of doing this in Smartsheet?

 

Paul

Conditional_Format_-.JPG

Blank_cell_String.JPG

Comments

  • Hi Paul,

    Try not using an "else" condition in your IF statements. If none of the conditions are met, the IF statement won't return anything. For example:

    =IF([Assemblage Mecanique]8 = 1, "N/A", IF(Coupe8 = 1, "N/A"))

    If neither of your columns has a checked box, nothing will be returned.

  • Hi Shaine,

    Duh! thank you for taken my head out of the sand, YES, works like a charm.

    Best,

    Paul

Sign In or Register to comment.