Formula to filter for active rows

jspark
jspark ✭✭✭
edited 06/15/23 in Formulas and Functions

I am using a sheet to track many different projects, some of which have milestone subrows, some which do not. I want to be able to filter out projects that have been fully completed (either no child rows and status = completed OR all child rows = completed and parent status = completed) but show the milestone subrows for projects that are ongoing regardless of the subrow's status.

I cannot use the sheet filter function since you cannot have both and + or conditions. Instead, I am trying to create a helper column to evaluate if a row is "active".

I want to have the following rows marked as ACTIVE:

If it is a row with Child Rows = 0 and Status = "In Progress" or "Preparing"

If is a row with an ancestor and the ANCESTOR's Status = "In Progress" or "Preparing"


marked as INACTIVE:

If it is a row with Child Rows = 0 and Status = "Paused" or "Cancelled"

If it is a row with an ancestor and the ANCESTOR's Status = "Paused" or "Cancelled"


marked as COMPLETED:

If it is a row with Child Rows = 0 and Status = "Completed"

If is a row with an ancestor and the ANCESTOR's Status = "Completed"

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @jspark

    Ah yes of course! My apologies, try adding this statement in:

    =IF(OR(Status@row = "Cancelled", Status@row = "Paused", PARENT(Status@row) = "Cancelled", PARENT(Status@row) = "Paused"), "Inactive", IF(OR(AND(COUNT(CHILDREN()) > 0, Status@row = "Completed"), PARENT(Status@row) = "Completed"), "Completed", "Active"))


    This will only say "Completed" when the current row says "Completed" IF it has Child Rows. Otherwise it will check the Parent row value for Completed.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Hi @jspark

    If we start with your second two instructions it will be a bit more simple to write - then anything leftover is "Active".

    Try this:

    =IF(OR(Status@row = "Cancelled", Status@row = "Paused", PARENT(Status@row) = "Cancelled", PARENT(Status@row) = "Paused"), "Inactive", IF(OR(Status@row = "Completed", PARENT(Status@row) = "Completed"), "Completed", "Active"))


    Let us know if that gives you the correct label for all of your rows! If not, it would be helpful to see a screen capture of your sheet with the hierarchy and statuses, identifying what rows have the wrong active label (but please block out sensitive data).

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • jspark
    jspark ✭✭✭

    Hi Genevieve,

    Thank you! It works other than for the child rows that are completed but under an active parent. They should be active but are currently showing up as completed.


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @jspark

    Ah yes of course! My apologies, try adding this statement in:

    =IF(OR(Status@row = "Cancelled", Status@row = "Paused", PARENT(Status@row) = "Cancelled", PARENT(Status@row) = "Paused"), "Inactive", IF(OR(AND(COUNT(CHILDREN()) > 0, Status@row = "Completed"), PARENT(Status@row) = "Completed"), "Completed", "Active"))


    This will only say "Completed" when the current row says "Completed" IF it has Child Rows. Otherwise it will check the Parent row value for Completed.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • jspark
    jspark ✭✭✭

    That's perfect, thank you @Genevieve P.!

  • jspark
    jspark ✭✭✭

    @Genevieve P. Sorry, I spoke too soon.

    Rows with no children that are Status "Completed" are being marked as "Active" rather than "Completed." The rows with children seem to be categorized correctly.

  • Hey @jspark

    Good call-out. Instead of looking to see if the current row has Child rows, lets instead check to see if the current row has a Parent row or not:

    =IF(OR(Status@row = "Cancelled", Status@row = "Paused", PARENT(Status@row) = "Cancelled", PARENT(Status@row) = "Paused"), "Inactive", IF(OR(AND(COUNT(ANCESTORS(Status@row)) = 0, Status@row = "Completed"), PARENT(Status@row) = "Completed"), "Completed", "Active"))

    Does that now work for all rows?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!