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.
-
Happy to help. 👍️
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
