Formula Help - Childre

juwilson

I recognize that this is probably very easy and there are several topics on this already in the Smartsheet community. I swear I have searched them all and the suggested solutions are just not working for me. Any guidance is appreciated.

I've created a very generic task list, that includes columns for Start Date and End Date. These tasks have subtasks. I want the earliest start and latest end date to roll up to the parent task like in a project plan. I have tried:

=MIN(CHILDREN({Start Date}:{Start Date})

And many variations on that, but everything comes back unparseable.

Can someone please tell me how to either adjust my formula or just enable the roll-up functionality on my simple task list?

Thank you in advance

KPH

Hi @juwilson

There is a problem with the way you are referencing the cells to be evaluated.

To reference a column within the same sheet you use [ ] not { }. For example [Column Name]:[Column Name]

The { } are used for cross-sheet references (and are only needed once). For example {Column in sheet A}

As your formula is in the same sheet as the data you use the [ ] style.

But for this formula, looking at CHILDREN, you don't need to reference the whole column as you just need the children of that particular row, so you would use the reference for the cell. This is [column name]@row rather than [Column Name]:[Column Name].

So your formula would be

=MIN(CHILDREN([End Date]@row))

=MIN(CHILDREN([Start Date]@row))

If your formula is going into the end date and start date columns (ie you want the MIN of the CHILDREN in the same column) it is even more simple:

=MIN(CHILDREN())

KPH

Hi @juwilson

There is a problem with the way you are referencing the cells to be evaluated.

To reference a column within the same sheet you use [ ] not { }. For example [Column Name]:[Column Name]

The { } are used for cross-sheet references (and are only needed once). For example {Column in sheet A}

As your formula is in the same sheet as the data you use the [ ] style.

But for this formula, looking at CHILDREN, you don't need to reference the whole column as you just need the children of that particular row, so you would use the reference for the cell. This is [column name]@row rather than [Column Name]:[Column Name].

So your formula would be

=MIN(CHILDREN([End Date]@row))

=MIN(CHILDREN([Start Date]@row))

If your formula is going into the end date and start date columns (ie you want the MIN of the CHILDREN in the same column) it is even more simple:

=MIN(CHILDREN())

juwilson

Thank you so much! I appreciate both the formula help and the detailed explanation.

KPH

No problem. I’m pleased it helped.