Parent Row to display Child Row value based on upcoming due date

Luke W. · 12/14/22

I need to create a formula where the parent row determines what it displays in its cell based on the following criteria:

So 2 columns...

VNT and VNT Due

123 1/1/11

456 2/2/22

I want the VNT parent row to display the VNT child value that has the earliest date in VNT Due.

I hope that makes sense.

Thank you for any help!

Ryan Sides · 12/14/22

@Luke W. You'll need to use INDEX/MATCH formula combo to find the CHILDREN. Place this on the parent row in the VNT column

=INDEX(CHILDREN(), MATCH([VNT Due]@row, CHILDREN([VNT Due]@row), 0))

Ryan Sides · 12/14/22

Hey @Luke W. You're looking for the function combination of MIN and CHILDREN

MIN displays the lowest number, or in this case the earliest date.

CHILDREN indicates the subsequent rows in a hierarchy.

So if your data is setup like this...

You can use this formula to get the earliest date of the children of that parent...

=MIN(CHILDREN())

Does that help?

Luke W. · 12/14/22

Hi @Ryan Sides, that makes sense for sure. I currently have the date part taken care of, just like in your example.

What I also need, is for the "123" to be displayed in the "Parent" Row/Cell of VNT, since that is the earliest date of "12/14/22".

Ryan Sides · 12/14/22

@Luke W. You'll need to use INDEX/MATCH formula combo to find the CHILDREN. Place this on the parent row in the VNT column

=INDEX(CHILDREN(), MATCH([VNT Due]@row, CHILDREN([VNT Due]@row), 0))

Luke W. · 12/14/22

That's what I needed! Thank you @Ryan Sides

Ryan Sides · 12/14/22

Glad to hear it! Happy to help

Best Answer