Column Formula with different formulas in parent and child

Christine Ronsholdt · 04/19/22

Hi,

I'm trying to make a column formula, where the parent cell (level 0) will return 'Delayed' if any child cells have the value 'Overdue' in it. The child cells currently have the below formula in it, which I would like to extend to be able to make it a column formula.

=IF([Days left]@row < [Estimated effort]@row, "Overdue", "")

I hope you can help.

Thanks, Christine

Paul H · 04/19/22

@Jeff Reisman

What about this as a way to do in on one column formula

=IF(COUNT(CHILDREN()) = 0, IF([Days Left]@row < [Estimated Effort]@row, "Overdue", "On Time"), IF(CONTAINS("Overdue", CHILDREN()), "Delayed"))

Note, time vs effort cannot be blank, needs something to count.

Jeff Reisman · 04/19/22

@Christine Ronsholdt

You can only have one column formula in a given column. That means if you want the child rows to have a column formula, the column you want to generate "Delayed" in on the parent rows must be a different column. We'll use a helper column for this. Call it "ParentStatus".

Now the good news is that both most likely can be column formulas. If your Parent rows do not have any values in the [Days left] and [Estimated Effort] columns, then your column formula for creating "Overdue" will leave that column blank on parent rows. We'll edit that formula further down below.

In your "ParentStatus" helper column, where you'll want to generate "Delayed" for parent rows with overdue children, use something like the following:

=IF(COUNTIFS(CHILDREN([Time vs Effort]@row), @cell = "Overdue") > 0, "Delayed", "")

This will leave the child rows blank; because those rows don't have child rows of their own, they evaluate to false.

On your child rows, update your formula in [Time vs effort] column to account for the parent rows that show Delayed:

=IF(ISTEXT(ParentStatus@row), ParentStatus@row, IF([Days left]@row > [Estimated effort]@row, "Overdue", ""))

In English: If the ParentStatus column on this row has a text value in it, put that value in this [Time vs effort] column, otherwise run this IF formula to check if [Days left]@row is greater than [Estimated effort]@row, and if it is, put "Overdue" in this cell, otherwise, leave the cell blank.

Convert both to column formulas, and once you're sure it's working, you can hide the "ParentStatus" column.