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

edited 10/18/23

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

can anyone help me?

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

They this:

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

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

• ✭✭✭✭✭✭

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

• I get no output at all nothing happens

• ✭✭✭✭✭✭

Are you able to provide a screenshot?

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

• ✭✭✭✭✭✭

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.

• 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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!