Parent Row Roll up using a specific text value

Community:

I am piggy-backing off Nathan Skyers' request from November 24, 2016 1:13 pm, regarding Parent Row Roll up, as I need a very similar formula.

Any help will be appreciated.

Thank you,

Sandy

Is it possible to have a text value show in a parent row whether all statuses match my criteria OR if even one status matches my criteria?

In my [Project or Task Status] column, I have these droplist values: "Not Started", "In Progress", or "Complete."

NOTE: I am not using dependencies if this matters.

(a) If even one of the child statuses is set to "In Progress" or "Not Started" or "Complete", I would like that particular status to show in the parent row.

(b) If all child statuses are set to "In Progress" I would like "In Progress" to show in the parent row.

(d) If all child statuses are set to "Not started" I would like that to show in the parent row.

This will allow me to see the status of a sub-project in my master gantt sheet by using only the at the parent row.

Find more posts tagged with

Sheets

Healthcare

Formulas

Project Management

Comments

Preston

Try this: =IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") > 0, "Complete", "Verify")))

This assumes priority from highest to lowest is

Not Started
In Progress
Complete

Also, if there is not at least one of the above values found in the children this formula will mark the parent row "Verify". This might help spot erroneous entries if your dropdown list is not restricted to listed values.

Good luck

Preston

sdkotes12901

Preston:

Thank you for your help. This is a great start. I will work with this formula, as I am not sure that I am getting exactly what I want.

I really appreciate your input.

Sandy

sdkotes12901

Preston:

Correction. It seems to be working. I have grandchildren so I am making sure all roll up to the child and parent.

(Takes me a minute I!)

Thank you

Sandy

Preston

Understood. To take this further, you will need to place the formula in all parent rows.

Grandparent - formula
- Parent - formula
  - Child - value selected from dropdown list

This approach will roll everything up into the "master" parent roll regardless of how may children there are.

Preston

sdkotes12901

Preston:

Perfect. I had started that process, which is why I made a correction. This is a fabulous solve!

Sandy

Nathan Skyers

I appreciate the follow up Sandy, I'll give this a try as soon as I have a minute. Thanks!

Vasiles Kiosses

Dear Smartsheet Colleagues.

I have used this thread to almost complete a problem I have. I followed the same conditions however the difference in my requirement is, that

if all the children are "Complete" then the status is "Complete",

however if for example one of the children is "Complete" and the others are either "In Progress" or "Not started", then the status should show "In Progress",

If the entries are anything other than ALL "In Progress", "Not Started" or "Complete", then it should be set to "Verify".

Thanks in advance for your time and support.

Preston

Vasiles,

You should be on the right path. I believe the key point is making sure you have your argument in the correct order. Arguments are read left to right. So, your argument should check for "Complete" first, then "In Progress", then "Not Started", and lastly "Verify". This is very similar to the formula I use on my parent roll up. Best of luck!

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Update") >= 1, "Update", IF(COUNTIF(CHILDREN(), "") >= 1, "Pending CO", IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", "TBD"))))))