Formula to filter for active rows
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
-
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 -
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.
-
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 -
That's perfect, thank you @Genevieve P.!
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!