How can I have the Parent Line update with date only after all the children lines are completed

bking8

Above is my spreadsheet. The Blue line is the overall project info. The gray lines are different phases and white lines are the work within those phases. I don't understand the dependencies concept and its causing end dates to populate on the blue/gray lines when all the white lines are not complete. What I'd like to happen is that once all the white lines have a populated end date, the gray line will update with the latest date. Once all the gray lines have a populate date, the blue line will populate with the latest date. The end date column is contigent upon the Status Colum that should say complete across the board...

Genevieve P.

Hiya! Just jumping in here to clarify - if you're using Dependencies in your Gantt sheet (check Project Settings) then you won't be able to add formulas to your date columns. There's already a back-end formula that will be working to make sure the Parent Rows are summarizing the dates of the Child Rows. Here's the Help Article that goes through this: Parent Rollup Functionality

However if you're just using Gantt, without dependencies, I agree with @Leibel Shuchat's formula above!

=IF(Status@row="Complete",MAX(CHILDREN()),"")

This first checks the status in the current row to see if it's Complete, and if it is, returns the MAX date of specifically just the child rows of the current column, see: CHILDREN Function

Have you tried the formula?

Cheers,

Genevieve

Leibel S

On the parent rows in the End Date column put the below formula:

=MAX(CHILDREN())

bking8

@Leibel Shuchat - I did that, unfortunately its entering dates prior to all the children rows being entered

I don't want the gray line to have anything until all the white lines are filled in - could this be a formatting issue?

Leibel S

@bking8

How is the status column updating?

You can use that

=IF(Status@row="Complete",MAX(CHILDREN()),"")

bking8

Status Column is manual update - at least the white lines are, I want the gray line to update to the appropriate status based on all the children lines as well

BullandKhmer

Are you trying to use a gantt chart to track actuals (past dates) only and not forecasting? If so, that doesn't make very much sense.

Also, in the Gantt layout the start date and end date autopopulate for all parent rows based on the earliest and latest start and end dates respectively for child rows. So, Smartsheet is functioing correctly, it just seems you dont fully understand it.

I suggest using gantt charts as intended and forcasting start dates and durations for all tasks and then this wouldn't really be an issue.

But if you insist then a work around would be setting up a NON-Gantt sheet with start, end and duration columns. Then you could set up formula in the parent rows to calculate start, end, duration etc. upon completion of all child rows.

Its not like you need the gantt feature anyway, its fairly pointless visualizing dependencies on already completed tasks right?

bking8

@BullandKhmer Im sorry your approach to answering my question definitely isn't helping by telling me what is pointless or what I don't understand. I don't doubt that Smartsheet is functioning correctly. I am asking for this capability in a Grid layout so that the child rows get updated and once ALL are completed with a date, the parent row updates, similar to how I have it working for the status. The status of the parent row isn't showing "complete" until all the child rows show complete as well. I'd like the dates to function that way - if that is not something it can do, I'd rather the answer just be that w/o all the challenges to my intelligence. This is a new system I'm learning and would hope those who choose to help come across better than what you have.

Genevieve P.

Hiya! Just jumping in here to clarify - if you're using Dependencies in your Gantt sheet (check Project Settings) then you won't be able to add formulas to your date columns. There's already a back-end formula that will be working to make sure the Parent Rows are summarizing the dates of the Child Rows. Here's the Help Article that goes through this: Parent Rollup Functionality

However if you're just using Gantt, without dependencies, I agree with @Leibel Shuchat's formula above!

=IF(Status@row="Complete",MAX(CHILDREN()),"")

This first checks the status in the current row to see if it's Complete, and if it is, returns the MAX date of specifically just the child rows of the current column, see: CHILDREN Function

Have you tried the formula?

Cheers,

Genevieve

bking8

@Genevieve P. - I had not tried that as of yet, your and @Leibel Shuchat 's solution worked for me . Thanks for clarifying for me