Finding date in a child when other children are not always dates

Frank Falco

I have a parent row that I am trying to use to summarise the Children rows.

One column is a date called CloseDate.

Rows are added every week, that create in effect a weekly record and the CloseDate is set to the word Active until the entry the week that it is closed and then a date is added. The row is not always at the top or bottom of the Children.

I would like the CloseDate in the Parent row to show Active until there is a row with a date in the CloseDate of one of the Children, then it should show the date in the child's CloseDate.

See the picture attached to see what I mean. The green cell is the parent and that is where it should show either Active or the date in the child row.

I have also considered using a column next to the CloseDate to set a flag using the IsDate() to set the checkbox but cannot work out how to use that to find the CloseDate as using VLOOOKUP as CHILDREN(CloseDate@row) does not seem to work for setting ranges.

I appreciate any assistance with this.

Frank Falco

I used =MAX(CHILDREN()) and that seems to have worked, sometimes the simplest things work best.

Complete script is =IF(NOT(ISDATE(MAX(CHILDREN()))), "Active", MAX(CHILDREN()))

Andrée Starå

Hi Frank,

Nicely done and yes, sometimes it's the simplest solution that's the best!  

Thanks for sharing!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting