How Do I link the Status Column to the % Complete Column?

I am looking for a formula that will allow me to update the Status Column (not Schedule Health) to update based on the % Complete (or vice-versa).

Example: If the % Complete is updated to 100% the Status should auto-update to Complete. If the % Complete is anything between greater than 0% and less than 100% the status should update to In Progress.

Alternatively, if the Status is updated to Complete, then the % Complete should be auto-updated to 100%.

Any insight is appreciated.

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Hi BArmendariz,

    It is impossible to build formula for Status and % Complete columns to circle reference, this will lead to error warning.

    I recommend to build two "Change cell value" automation workflows to update these 2 columns.

    One workflow to update Status based on % Complete and one workflow to update % Complete based on Status

    Look at the screnshots below for your reference.

    Hope it helps.


    Gia Thinh Technology - Smartsheet Solution Partner.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Hi BArmendariz,

    It is impossible to build formula for Status and % Complete columns to circle reference, this will lead to error warning.

    I recommend to build two "Change cell value" automation workflows to update these 2 columns.

    One workflow to update Status based on % Complete and one workflow to update % Complete based on Status

    Look at the screnshots below for your reference.

    Hope it helps.


    Gia Thinh Technology - Smartsheet Solution Partner.

  • @Gia Thinh

    Thank you so much; this was a great kick-starter. I added a few more parameters to extend the workflow for additional variables, and it worked perfectly.

    The piece I was missing was that this had to be performed as a workflow rather than a formula. 😊

    Your help and insight is much appreciated!

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Happy to help.


    Gia Thinh Technology - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!