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 Admin
    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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 Admin
    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

  • 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.

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!