How to display a MIN date on parent row when all children date rows contain a formula

I have a date column (Start Date) which is being populated from a Vlookup which references a separate calendar table. For the Parent Rows of the populated date column (Start Date), I would like to display the minimum date of its children.

Formula used in the Parent cell: =MIN(children())

Result: no error is thrown, but no information is populated

Assumption: The MIN formula does not work when used with Children cells that are populated with a formula.

This sheet must be automated as the dates change frequently, so I can't copy/paste the dates into another column to then do the MIN calculation there.

Does anyone know a way to auto populate the Parent Dates in this scenario? Thanks in advance for your help!!

Noel

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭

    I think I might know what is going on looking at your screenshots. I recreated the scenario you described and it worked for me.

    It looks like you want the start date to show on the first row, is the second row indented and then the other rows below that indented one more level?

    If I do that, I get the same behavior

    But if I use Min(Descendants()) I get what I need.

    Hard to say what the best solution is without seeing more of your heirarchy/setup. But you could use Descendants instead of Children or point the Children reference to the row below, so =MIN(CHILDREN([Start Date]2)) or something along those lines.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!