Index/Collect from Descendants

Leeweber

In this sheet, I have a column [Groups] which is a parent column to multiple "Groups", each of which is a parent column itself to tasks associated with each group.

I need help putting a formula in [Start Date] of the parent row that would give me the very 1st value from [Date] column that is a Descendant of [Groups]@row

This formula gives me the correct row # from the Groups column
=INDEX(DESCENDANTS(Groups@row), 2)

This formula is giving me an error
=INDEX([Date]:[Date], DESCENDANTS(Groups@row), 2)

Thanks in advance!

Kelly Moore

Hey @Leeweber

I was hoping to do this without adding a helper column but I could not. Unfortunately my original formula isn't really working - it is only pulling the first date which coincidentally was your minimum. My mistake.

This helper column is one I always add to my sheets when I have hierarchies that begin to include grandparents and beyond. Uncreatively, I call this helper "Level". As with most helper columns you can push this column to the far right and hide, if desired.

Level

=COUNT(ANCESTORS())

This is an easy way of designating specific levels of hierarchy in both formulas and Reports. It's also the only way to gather the data if using cross sheet references.

Your desired formula is then much easier:

=IF(AND(COUNT(CHILDREN(Groups@row)) > 0, Level@row=0), MIN(COLLECT(Date:Date, Date:Date, ISDATE(@cell), LEVEL:LEVEL, @cell = 2)))

And you can swap the MIN and MAX for th e END Date calculation or easily gather a different level of hierarchy.

Will this work for you?

Kelly

Kelly Moore

Hey @Leeweber

Try this:

=IF(AND(COUNT(CHILDREN(Groups@row)) > 0, COUNT(ANCESTORS(Groups@row)) = 0), MIN(COLLECT(Date:Date, Date:Date, ISDATE(@cell ), Date:Date, @cell = INDEX(DESCENDANTS(Date@row), 2))))

I wrote the formula this way to givh1, Group 2, etc individual groups. This formula will allow you build additional IFs onto the formula. If you need the End Date, it is the same formula but swapping MAX for MIN.

Will the formula above work for you?

Kelly

Leeweber

@Kelly Moore

Your formula works perfect for collecting the start date, thank you!

Could you kindly assist with getting end date?

Just changing MIN to MAX in the formula you provided still gave me 2-Dec-2024 (possibly because of this part of the formula @cell = INDEX(DESCENDANTS(Date@row), 2)

I adjusted the formula to this for the "End Date"

=IF(AND(COUNT(CHILDREN(Groups@row)) > 0, COUNT(ANCESTORS(Groups@row)) = 0), MAX(COLLECT(Date:Date, Date:Date, ISDATE(@cell ), Date:Date, @cell = INDEX(DESCENDANTS(Date@row), COUNT(DESCENDANTS(Date@row))))))

But I'm getting 3-Feb-2025 and not 17-Feb-2025.

Please let me know your thoughts 😊

And Happy Thanksgiving!

Kelly Moore

Hey @Leeweber

I was hoping to do this without adding a helper column but I could not. Unfortunately my original formula isn't really working - it is only pulling the first date which coincidentally was your minimum. My mistake.

This helper column is one I always add to my sheets when I have hierarchies that begin to include grandparents and beyond. Uncreatively, I call this helper "Level". As with most helper columns you can push this column to the far right and hide, if desired.

Level

=COUNT(ANCESTORS())

This is an easy way of designating specific levels of hierarchy in both formulas and Reports. It's also the only way to gather the data if using cross sheet references.

Your desired formula is then much easier:

=IF(AND(COUNT(CHILDREN(Groups@row)) > 0, Level@row=0), MIN(COLLECT(Date:Date, Date:Date, ISDATE(@cell), LEVEL:LEVEL, @cell = 2)))

And you can swap the MIN and MAX for th e END Date calculation or easily gather a different level of hierarchy.

Will this work for you?

Kelly

Leeweber

@Kelly Moore this is perfect, thank you very much for your help!

Best,

Lee