Hi Community. Is there a template for Gantt chart that has conditional format for statuses?

gene2525

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.

KPH

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

KPH

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

gene2525

Hi KPH,

Perfect. I will give that a try, thanks for your help.