Using Parent / Children functions to roll up dates

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.

Find more posts tagged with

Formulas

Comments

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.

Rollup MS.png

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?