Conditional: If one child is closed or past due for approval, entire hierarchy/project is closed

aaboueid · 02/28/18

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

Chris McKay · 03/01/18

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.

aaboueid · 03/01/18

Thanks Chris, this worked flawlessly!

aaboueid · 03/01/18

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), "")

Chris McKay · 03/01/18

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.

aaboueid · 03/01/18

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

Conditional: If one child is closed or past due for approval, entire hierarchy/project is closed

Comments

Categories