Using Parent / Children functions to roll up dates

Ofir Marco

trying to add new columns to show some milestones end dates on the parent row.

For example, if i have a summary parent row called "Project 1" and one of the child tasks called "SOW", i want to show the SOW date rolled up to the summary row in a new custom date column using formulas.

Any idea how to do that?

Not so sure how to use the Children / Parent functions to get dates.

J. Craig Williams

Have you enabled Dependencies?

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

If there is something more that you are looking for, let us know.

Craig

Ofir Marco

Hi Craig,

Thanks for the response but i am not talking about dependencies at all.

It is about taking specific finish dates and roll them up on new columns on the summary row.  not dependent, but to the parent row.

See example.

 

Ofir Marco

What i need are the parts in the bold row.

J. Craig Williams

Are the three rows always in the same order?

Any rows likely to be added above, below, or between them?

If the same is yes and no, then you could just point to them

=[Ship Date]23 

if the parent is on row 22.

and so on for each of the three dates.

The above will still work if you later reorder the rows or add others. 

If these formulas are copied to another parent row, however, is when you have to manually update them.

If the manual part becomes a pain, then we can go into the harder solution.

I hope this helps.

Craig

 

Ofir Marco

Not always the same amount and order of rows, usually does, but not always.

I am thinking and trying a solution that is something like:

if for the current parent row one of the child rows task name contains "Ship" then take the Finish date to the Ship date column.

Same for other columns only with other text values.

Not sure if formulas allow it and how can I do that without the user need to change formulas all the time to adjust the amount of rows or order.

 

Ofir Marco

I think i figure out a solution!

I still need to think how to set a process that we will be able to use a fixed amount of rows below in each Summary.

What i do for each column is to set this formula on the child tasks (example for SOW date column):

=IF(FIND("SOW", [Task Name]5, 1) > 0, Finish5, "NA")

This will set a date only when the phrase searched for exists in the task name column.

Then, on each parent row i will use this formula:

=MAX([SOW date]4:[SOW date]11)

It will pull the max date of the rows with "SOW" in the task name.

Only issue is to take care of having a fixed set of rows and if rows are added it should be added in the middle and not at the end since those will be out of the formula range.

I think i can have a milestone row at the end (for example row 11 in this example) so that users will only insert above it.

J. Craig Williams

That might work.

Use 

=MAX(CHILDREN())

instead of the formula you have

Craig

Ofir Marco

Thanks.  Children function also works.

Is this function only works with this kind of numeric functions (Max, Min, Sum. COunt, etc.) or it can work in other ways or with other formulas?

Can it work well inside IF function with some logic?

Just thinking ahead for some more ways to use...

J. Craig Williams

Yes, CHILDREN() works with formulas that work on ranges of cells.

Depending on specifics, it can also work with IF (but not directly as IF does not expect a range)

Craig