Changing % complete using a formula in another cell


I know you cannot use a formula in a "% Complete" field. Is there a way to update "% Complete" from a formula in another column (something like a "helper" column)? I have a "Task Status" column with drop down values of Not Started, In Progress, and Complete. I would like to change the value of "% Complete" when I change the value of "Task Status". (e.g. If [Task Status] = "Complete" then [% Complete] = 1). Is this possible?


  • Genevieve P.

    Hi @BJ Patnode

    The % Complete column can't have formulas if you are using it for the Parent rollup functionality with weighted percents in Project Settings, and it's not possible to update it from a different column.

    That said, the % Complete automation in parent rows is optional. (Read more about this in our Help Center, here).

    To adjust this, you can open the Project Settings on the sheet and set the % Complete Column drop-down list to None. Doing so will enable you to use formulas in the parent rows or set the parent row values manually. (Keep in mind that it will also remove the progress indicator from the Gantt bars.)

    If you decide to go this route, you could input exactly what you wanted to say directly in your % Complete field:

    =IF([Task Status]@row = "Complete", 1) ... etc.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • BJ Patnode

    Thanks Genevieve, I was hoping to have both %Complete automation and a way to programmatically update the %s so that I could have the progress indicators in the Gantt chart. Oh well. Appreciate you answering the question. Thanks again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!