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

Paul Charlebois

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

Shaine Greenwood

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.

Paul Charlebois

Hi Shaine,

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

Best,

Paul