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

Options
Frank Falco
Frank Falco ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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.

Parent-child-dates.PNG


✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tags:

Comments

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    edited 07/03/19
    Options

    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()))


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Frank,

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

    Thanks for sharing!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!