Conditional: If one child is closed or past due for approval, entire hierarchy/project is closed
I have an approval request setup in my sheet, and an age column where I am tracking the number of days since the approval request was sent out. I need a method to mark the whole project tree "Closed" if any of the children's requests for approval's "age" is over (2) days
Comments
-
Hi aaboueid,
I'm still not clear how your sheet is setup but is the following what you're after?
=IFERROR(IF(MIN(CHILDREN([Approval Request Date]1)) < TODAY() - 2, 1, 0), "")
You could add some conditional formatting for the row based on the value in the Closed column.
-
Thanks Chris, this worked flawlessly!
-
Chris, on closer observation, it is working perfectly if there are no grandchildren, please see screenshot, the issue for review is (3) days old, I need the grandparent "LC Lemle" to be marked closed in the P_Closed column, not just the direct parent "Specs & Drawings"
Here is the formula in P_Closed column:
=IFERROR(IF(MIN(CHILDREN(Age@row)) > 2, 1, 0), "")
-
Hi aaboueid,
My pleasure. It's probably best to adapt what you have already instead of re-working it. How are you calculating the Age column? I'd tackle this by looking for:
=IF(COUNT(CHILDREN(Age1)>0,MAX(CHILDREN(Age1), FALSE) with FALSE being whatever formula you're already using in Age to come up with the 3 value.
You could also add another column to identify which rows are parents or children and use that in your formula to determine which Age value to check against.
-
Here is the function in the Age column:
=IF(ISBLANK(Issued@row), "", NETWORKDAYS(Issued@row, TODAY()))
And we have an"Is_Parent" check box column identifying parent rows
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 298 Events
- 33 Webinars
- 7.3K Forum Archives