Status Formula

Wendy Young

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?

Wendy Young

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

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?

Wendy Young

Yes, they are.

Paul Newcome

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

Wendy Young

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

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"))

Wendy Young

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

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?

Wendy Young

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

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"))

Wendy Young

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

Paul Newcome

Happy to help. 👍️