How to show if any child task has status 'in progress', parent row also 'in progress'?

Lisa McGonigle

I want it so that if any child task is 'in progress', the parent row shows as 'in progress'. (Shot 1)

I can get the first part of this set up in an individual task by using =IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress". (Shot 2)

BUT then when I try to apply it to the entire column/sheet, gets rid of the other statuses in the Status column (Not Started, Complete) (Shot 3)

What does the rest of my formula need to be?

Shot 1.png

Shot 2.png

Shot 3.png

Kelly Moore

If you can define those states we can make the status automatic and your process will be more consistent in reflecting the status.

If you search the community for "IF statements for RYG balls" or "IFs and Status", you will find many examples of what others are using as their criteria for their status. Maybe these will help you to begin to define your conditions- or at least version1 of the conditions. Formulas can be tweaked over time.

If you decide on criteria, give me a shout and I'll be happy to help you write your specific formula.

Kelly

Kelly Moore

Hello @Lisa McGonigle

What are the conditions for the other responses? We can build the formula that will select the appropriate response if you can define the conditions. You cannot have manual entry and formulas in the same column.

Let me know how you want to proceed and I'll be happy to help you with the formula

Kelly

Lisa McGonigle

Thanks Kelly! The conditions are:

• if any of the child tasks are In Progress, the parent status is in Progress, i.e.; if there are five tasks, even if four of them are complete, or if four of them are not started, but the remaining child task is In Progress, then the parent status is In Progress.
• if all of the child tasks are Not Started, the parent status is Not Started.
• if all of the child tasks are Compete, the parent status is Complete

Does that explain it?

Kelly Moore

Hey Lisa

Are the Child row status already being determined by a formula? If yes, then what is that formula?

The status for the Child rows is in the same column as the Parent row- this is what I was referring to when I mentioned a column has to be either all manual or by formula. Yes, it is possible to manually enter the formula in the Parent row each time a Parent row is entered but that means there will always be the chance a row is missed - and it is a burden on someone to keep up with it.

Lisa McGonigle

Ah I see what you mean. Child row not currently being set up a formula but being done manually.

Kelly Moore

If you can define those states we can make the status automatic and your process will be more consistent in reflecting the status.

If you search the community for "IF statements for RYG balls" or "IFs and Status", you will find many examples of what others are using as their criteria for their status. Maybe these will help you to begin to define your conditions- or at least version1 of the conditions. Formulas can be tweaked over time.

If you decide on criteria, give me a shout and I'll be happy to help you write your specific formula.

Kelly