Hi Community. Is there a template for Gantt chart that has conditional format for statuses?
I've set up a Gannt chart with a manual column w/drop down list for statuses of Complete, In Progress, On Hold, Not Started. I want this column to look at the "% Complete" column and if 100% change status of each task to Complete; if < 100, In Progress; if 0% Not Started, if 1= On Hold.
Thanks for any help that you can give me.
Best Answer

Hi @gene2525
Conditional formatting will change the format of a cell based on a rule. It will not populate the cell. To change the value of the cell based on another cell you can use an IF function. You would need to put a formula in the Status column.
Assuming your % Complete column is set up as percentage format (and therefore 100% is 1), this formula will return "Complete" if the % Complete column on that row is 100%, and "Not Complete" if it is not.
=IF([% Complete]@row=1,"Complete","Not Complete")
You can then replace the "Not Complete" with another IF function. The new part, in bold here, now checks if the % Complete is 0% and if so returns "Not Started". If neither IF is true it returns "Not Complete or not Not started".
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", "Not Complete or not Not started"))
We can extend it further like this. So if the first two IFs are false it now checks if the % Complete is 100% and if so returns "On Hold".
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", IF([% Complete]@row = 1, "On Hold", "Not Complete or not Not started or On Hold")))
And now we can simply change the words for when all are false to be In Progress (this will be anything that is not 100%, 100% or 0%.
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", IF([% Complete]@row = 1, "On Hold", "In Progress")))
There is more information about IF here: https://help.smartsheet.com/function/if
Answers

Hi @gene2525
Conditional formatting will change the format of a cell based on a rule. It will not populate the cell. To change the value of the cell based on another cell you can use an IF function. You would need to put a formula in the Status column.
Assuming your % Complete column is set up as percentage format (and therefore 100% is 1), this formula will return "Complete" if the % Complete column on that row is 100%, and "Not Complete" if it is not.
=IF([% Complete]@row=1,"Complete","Not Complete")
You can then replace the "Not Complete" with another IF function. The new part, in bold here, now checks if the % Complete is 0% and if so returns "Not Started". If neither IF is true it returns "Not Complete or not Not started".
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", "Not Complete or not Not started"))
We can extend it further like this. So if the first two IFs are false it now checks if the % Complete is 100% and if so returns "On Hold".
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", IF([% Complete]@row = 1, "On Hold", "Not Complete or not Not started or On Hold")))
And now we can simply change the words for when all are false to be In Progress (this will be anything that is not 100%, 100% or 0%.
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not started", IF([% Complete]@row = 1, "On Hold", "In Progress")))
There is more information about IF here: https://help.smartsheet.com/function/if

Hi KPH,
Perfect. I will give that a try, thanks for your help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!