Formula to filter for active rows

jspark

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"

Genevieve P.

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

Genevieve P.

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

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.

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

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

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.

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