Hello, i need some help how do i connect the %complete column to the status column

Younes Amine
Younes Amine ✭✭
edited 10/18/23 in Formulas and Functions

for example if the status says in progress i want the %complete column to automatically say 50%

can anyone help me?

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 10/18/23 Answer βœ“

    You can do this with a nested IF statement, but you would need to outline what relationship you would want between the 2 columns - in your example the formula for % complete would be:

    =IF(Status@row = "In Progress", 0.5)

    The above assumes you've set the complete column to a percentage.

    However, you could set the % to give you the status instead:

    A very basic example:

    0 = Not started

    1-99 = In Progress

    100 = Completed

    This can be combined with other variables (e.g. estimated completion date) as well.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    It looks like you are using dependencies. In that case you will not be able to use a formula in any column being used by the dependencies.

    You will need to flip your logic so that the Status us generated by the % Complete, and the % Complete will have to be the manual entry.

    We could also use a couple of Change Cell automations instead of a formula in the Status column to allow for manual selection of "Waiting" as needed.

    The automation would be set up to run whenever the % Complete changes to any value. You would use a condition of the % Complete being zero to output Not Started, condition of one to output Complete, and condition of greater than zero and less than one to output In Progress.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!