Counting children in specific column (resolved)

Jonathan Hersey

I have a sheet that I'm using to keep track of about 100 projects, eventually each will have several child rows.  I'm not using sheet dependencies because I want my parent row to represent how many days are left on the project.  To do this I have 6 rows: "Start", "End", "Duration", "Real Start", "Real End" , "Real Duration".  You input your data into the first three, with the later 3 containing formulas.  My Gantt chart references the 3 columns that contain "real" at the start.    When I put in the data for a parent row/project, the gantt chart will show a start, end, and duration exactly as I put it in(this is the estimated project duration).  However, when I add child rows, I want my parent row "real start" date to be the Max Children END date +1, I want my "real duration" to be "Duration"-sum of all children durations, and my "real end" date to be "real start" + "real duration".

 

My issue is that in my "real start" cell of my parent row, I don't know how to write a formula that takes the Max Child in my "end" date  column.

 

If I can just get the "real start" formula to work the others will be easy.  Here's what I've tried: this returns the max of all children rows across all projects, I only want it to return the max of the children under that respective parent row

=IF(COUNT(CHILDREN()) = 0, [Start Date]1, MAX(CHILDREN([End]:[End]))+1)

 

Jonathan Hersey

I figured it out.  My issue was that I didn't understand you need to reference the parent cell in the column I wanted to get the Max child from.