Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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.
(c) If all child statuses are set to "Complete" I would like "Complete" 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.
Comments
-
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
-
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 -
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
-
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
- Parent - formula
This approach will roll everything up into the "master" parent roll regardless of how may children there are.
Preston
- Grandparent - formula
-
Preston:
Perfect. I had started that process, which is why I made a correction. This is a fabulous solve!
Sandy
-
I appreciate the follow up Sandy, I'll give this a try as soon as I have a minute. Thanks!
-
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.
-
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"))))))
