Status Formula

Can anyone help me figure out how to identify a project status as "Complete" only if all of the tasks in the entire column are complete, or as "In Progress" if there any tasks that are not complete?

Best Answer

  • Wendy Young
    Answer ✓

    Okay, the unindented worked when I adjusted the row references, however, will I have to go in and adjust that every time a new task is added?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are all of your other columns next to each other? Are you able to provide a screenshot of your sheet with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

    thinkspi.com

  • Yes, they are.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Oh. My apologies. I was picturing it differently. Are the light blue rows indented below the dark blue?

    thinkspi.com

  • Not on this sheet. However, I have other sheets that do have indented rows that will require this same functionality.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So for unindented rows, you will need to maually specify the range using row numbers such as

    STATUS2:STATUS53


    For indented rows, we can use

    CHILDREN()


    I will provide both for you.

    Unindented

    =IF(COUNTIFS(STATUS2:STATUS53, "Complete") = COUNT(STATUS2:STATUS53), "Complete", IF(COUNTIFS(STATUS2:STATUS53, "Not Started") = COUNT(STATUS2:STATUS53), "Not Started", "In Progress"))


    Indented

    =IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

    thinkspi.com

  • Thank you. I must be doing something wrong. The formula for the unindented is giving me an unparseable response. The formula for the sheets with indented rows is only giving me a response of "complete" regardless of the status of the other tasks.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For the unindented you will need to adjust the row references in the ranges to cover the actual area you want to reference.


    Can you provide a screenshot of both formulas in the sheet similar to the screenshot below?



    thinkspi.com

  • Wendy Young
    Answer ✓

    Okay, the unindented worked when I adjusted the row references, however, will I have to go in and adjust that every time a new task is added?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No. I completely forgot about something relatively new for Smartsheet. As long as some specific rules are followed, you are able to create a single circular reference within a column without throwing the error.

    For your particular case, the unindented Status formula should not break any of those rules. Remove the row references and reference the entire column. This should keep you from having to manually update the range.

    =IF(COUNTIFS(STATUS:STATUS, "Complete") = COUNT(STATUS:STATUS), "Complete", IF(COUNTIFS(STATUS:STATUS, "Not Started") = COUNT(STATUS:STATUS), "Not Started", "In Progress"))

    thinkspi.com

  • It's working! Thank you so much!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com