Trying to set a column formula without it marking blank rows!

Hello! I am running into an issue where I am trying to have the status bubble change colors based on status of the project. Red, yellow, green, gray. I have got that part figured out, but when I go to make it a column formula so that it applies to all rows, even new ones that are added, it puts the red bubble in rows that don't have any information in the primary column yet. I am trying to create a template so I want to have these blank rows here, but I don't want there to already be a red bubble until information is entered into the due date sections. Here is my current formula: =IF(Complete@row = "Not Started", "Gray", IF(Complete@row = "Complete", "Green", IF([End Date]@row <= TODAY(0), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))

Here is what is happening:


Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    This will set the cell to "blank" if the Complete column is blank for that row.

    =IF(ISBLANK(Complete@row), "", IF(Complete@row = "Not Started", "Gray", IF(Complete@row = "Complete", "Green", IF([End Date]@row <= TODAY(0), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!