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

Younes Amine

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

can anyone help me?

Nick Korna

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

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

Happy to help. 👍️

Paul Newcome

What are the rest of your statuses and the expected output for each?

Younes Amine

I want in Progress to say 50%, waiting also 50% and complete 100% and not started 0% thanks for your help

Nick Korna

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

They this:

=IF(Status@row = "Complete", 1, IF(Stauts@row = "Not Started", 0, .5))

Younes Amine

So i copy pasted your formula into the complete column of that line but it doesnt work what is wrong?

Paul Newcome

It depends. Are you getting an error message or an unexpected output?

Younes Amine

I get no output at all nothing happens

Paul Newcome

Are you able to provide a screenshot?

Younes Amine

i changed the words complete and not started to german bc my smartsheet is on german but it didnt change anything

Paul Newcome

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.

Younes Amine

ok i got it i found an article earlier on how to set an automation i will try it thank you very much for your help

Paul Newcome

Happy to help. 👍️