How Do I make a Gantt Chart automatically show 100% for Complete when Status is marked as "Complete"

I would like to automatically update the %Complete in the Gantt chart to 100% if the Status is set to "Complete" in the dropdown list. I can't seem to figure this out. I have tried entering calculations / IF("Complete", 100%, if(Not Started, 0%)) etc. but because there's parents calculations by default, these formulas cant be entered. I can manually update the % Complete for Children cells but this is cumbersome and can be prone to error.


Has anyone done this?

Tom

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @tommyW89

    You have the correct syntax for IF :

    =IF(logical_expression,value_if_true,[value_if_false])

    and nested IF where you replace the value if false with another IF:

    =IF(logical_expression,value_if_true,IF(logical_expression,value_if_true,[value_if_false]))

    I did notice two things:

    You need to include the cell reference in your logical expression. So to evaluate the "Status" column you would use:

    =IF(Status@row="Complete","100%") etc. I'm not sure if you are doing this.

    You may also want to make your new column a % format column and can then use "1" instead of "100%".

    The next step is to include a logical expression that first checks if the row is a parent before looking at the status column. You can do this by counting the number of child rows. If the row has more than 0 children, the formula can return nothing. If the row has 0 children, the formula to fill the various percentages can be used. Here is an example of how that IF would look:

    =IF(COUNT(CHILDREN(Task@row)) > 0, "this is a parent", "this is not a parent")

    Then you can replace the text strings with formula.

    Something like this:

    =IF(COUNT(CHILDREN(Task@row)) > 0, "this is a parent", IF(Status@row = "Complete", 1, IF(Status@row = "Not started", 0)))

    Then you can remove that placeholder text or replace it with the formula that you mention is used in the parent rows.

    =IF(COUNT(CHILDREN(Task@row)) > 0, "", IF(Status@row = "Complete", 1, IF(Status@row = "Not started", 0)))

    And make this a column formula. No need to edit anything in each row.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!