Yielding blank cells

Options
Esperanza Zagal
edited 07/31/20 in Formulas and Functions

Hello,

I'm using health circles to indicate progress. However, I want the circles (green, red, yellow, and gray) to show active stages in the process (complete, incomplete, in progress, on hold, respectively). I want phases that are not yet started, because the start date has not yet started or is not yet set, to remain blank...i.e. no health circle. Is that possible? I tried erasing start and end dates for these projects but a circle still pops up.

Thank you!

Esperanza

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    You can use the below formula (add it to the column with the Health symbols) assuming column names of Start Date, Stage, and Health. It will check to see if there is a date in the Start Date column, if yes, it will output the appropriate symbol based on Stage. If not, it will return blank.

    =IF(ISDATE([Start Date]@row), IF(Stage@row = "Complete", "Green", IF(Stage@row = "Incomplete", "Red", IF(Stage@row = "In Progress", "Yellow", IF(Stage@row = "On Hold", "Gray")))), "")

    It looks like this



    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

  • Esperanza Zagal
    Options

    Thank you Dan, this is useful. What if the start date is present but in the future? I'd like it to show as blank if we have not yet reached the planned start date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!