Formula for Status Ball

Options

Answers

  • janiebn
    Options

    I'm trying to do something similar but not sure how (sorry if my formula is in french)

    =IF([État de la tâche]1 = "Complété"; "Vert"; IF(AND([État de la tâche]1 <> "Complété"; TODAY() > [Date de fin]1); "Rouge"; IF(AND([État de la tâche]1 = "Pas Commencé"; "Bleu"; IF(AND([État de la tâche]1 = "En cours"; "Jaune"))))))

    This formula basicaly says if in cell 17 in a certain column i have selected either complete, not started or in progess from a dropdown menu then show a certain color symbol and if the task is not complete and the date is overdue then show a red symbol. What I would like to add to this formula if the cell is empty (didn't select anything from the dropdown) then leave the cell, where i ususally see a color symbol, blank. Right now i get a red symbol because i also don't have an end date to the task

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @janiebn

    You can add a statement right at the beginning that says if the cell is blank, return blank.

    I would also suggest changing your row numbers (eg. [État de la tâche]1) to @row ([État de la tâche]@row), so that you can make this formula a Column Formula.

    Try:

    =IF([État de la tâche]@row = ""; ""; IF([État de la tâche]@row = "Complété"; "Vert"; IF(AND([État de la tâche]@row <> "Complété"; TODAY() > [Date de fin]@row); "Rouge"; IF(AND([État de la tâche]@row = "Pas Commencé"; "Bleu"; IF(AND([État de la tâche]@row = "En cours"; "Jaune")))))))

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!