Hello, i need some help how do i connect the %complete column to the status column
for example if the status says in progress i want the %complete column to automatically say 50%
can anyone help me?
Best Answers
-
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.
Answers
-
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
-
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!
