Change status automatically when selecting % done



I have searched through the Help centre (center!) and I can't find the formula (if one exists....) that will automatically select the appropriate value in the % done when one of 6 choices from Status is chosen.

So when I select Not Started, I would like 0% to appear in the % Done, Started=25%, In progress=50%, complete = 100% etc

Any help, (even if it is the right article!) would be greatly appreciated



  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭

    If I understand correctly, you would like the % done to automatically generate based on someone manually selecting a status in the status column?

    Try this:

    =IF([Status]@row = "Not Started", 0, IF([Status]@row = "Started", 0.25, IF(.....)))

    Basically, use the IF formula and for the "False" part of the argument start a new IF statement until you have all of your statuses. I typically use "-" as the final false statement to tell me if there is something non-standard in the column.

    This will also work if you have the column formatted to a progress bar or to the RYGB balls (status bar use "Empty", "Half", "Three Quarter", etc. RYGB balls use "Red", "Blue")

    The numbers (0, 0.25, etc) assumes that you have the column set to percentage.

  • Kevin Nind

    Wow, ok I will stay behind tonight and try that out, thank you for putting effort into my problem!

    Yes both columns have 6 and 5 choices respectively

    0, 25, 50, 75, 100 % and corresponding "not started, waiting on client, in progress" etc

    I will post my success/failure!

    Thank you Danielle

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭

    You're welcome! Hope it worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!