Blank display value for Symbols type?

I'm trying to recreate a switch function I've used in Project files to work in Smartsheet. It updates a symbol status column based on %Complete, Today's date, and Finish dates. I'm trying to use the green Up, yellow Sideways, and red Down arrows.

Issue is, also need to display a blank in that status column if none of the conditions exist. For example, if the task is not completed, but the due date is 2 months in the future, I don't want to display any symbol. (The task isn't late, isn't complete, and isn't imminently due, so, visually, I want something very neutral to display.)

However, I can't seem to find a way to display "nothing" in a symbol cell if a symbol has ever been displayed. In essence, I can't restore the cell to its first state of blankness as a project is updated. Once a symbol has been displayed, I can never re-blank the cell.

I don't want a fourth symbol. A blank cell better signifies "not critical" than a fourth symbol. But, none of the symbol sets includes an original "off" status value as part of the set. Trying to use "" or " " throws an "Incorrect Argument" error.

Any ideas?

Lisa

Find more posts tagged with

Formulas

Conditional Formatting

Project Management

Comments

SmSulli

Lisa,

You can use a blank ("") in your IF formula and it will show a blank cell.

I made a quick example. If you want to show your actual sheet I or someone can make a more specific formula.

Notice the last Else is "". If Column6 is 1 or less it shows a blank cell.

=IF([Column6]1 > 4, "Up", IF([Column6]1 = 4, "Sideways", IF(AND([Column6]1 < 4, [Column6]1 > 1), "Down", "")))

JennS_

I need to do something similar to this where I want several rows at the top of my sheet to be blank cells, while the rows below are check boxes. It only will show the checkboxes. What formula should I use?

Need columns 3-7 to be blank, but the cells below to have the checkboxes.