Counting and applying "color status" for parent/children rows

I have overall Annual goals (purple lines) with sub projects that fall into a particular category. I then have subtasks that fall under the corresponding item (identified with an alphanumeric task number). I want to be able to summarize (on a dashboard) a couple of things:

the task health of the project level (task 25 with a green indication in "Master Project R/Y/G" ) as well as the count of only the main subtasks...so a total number of 3 on the Action Tracker 2019 screenshot...) I have a metric that shows all lines (Capture 2) but can't figure out the syntax to only count the tasks at the "parent" or singular task level - i.e. only the tasks with whole numbers (no letters assigned).

Thank you all so very much!

Capture 2.PNG

Find more posts tagged with

Formulas

Support

Comments

Alejandra

Hello,

You could enter the formula below in your status column to return the "weakest link". For example, if at least one child row has a red ball, the parent row will also display a red ball. If there isn't a red ball in the children rows, but there's at least one yellow ball, then the parent row will return a yellow ball, and so on:

=IF(COUNT(CHILDREN(Objective@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))))

Since the CHILDREN function references the direct children of a parent row and tasks 45, 66, and 25 are direct children rows of "SOLUTIONS, IMPLEMENTATION, AND PROJECT MANAGEMENT", you could consider entering the formula below in the Task column above task 45:

="Total Task Count: " + COUNT(CHILDREN(Objective@row))

For more informations on the functions used, see here:

IF: https://help.smartsheet.com/function/if
COUNT: https://help.smartsheet.com/function/count
CHILDREN: https://help.smartsheet.com/function/children
Formula Error Messages: https://help.smartsheet.com/articles/2476176-formula-error-messages

JFrye

Alejandra, thank you for your help. My boss wanted to change the structure, so I have deleted the purple lines and added the annual objectives to a Column (Objective/Grandparent). I still have the main projects identified by a numeric Task number and the subtasks have a number and a letter (61 is the main project and 61A is a part of that project. This is in Capture 1). Ideally, I want to be able to be able identify the aggregated completion percentage of each main project (tasks with only numbers) within each of the 4 main annual objectives (Objective/Grandparent column). I have averaged the %Complete (based on the date opened and est close dates with network days and divided how complete the project should be today - Col Est. Close %). If the Est Close % is less than the Task close%, the ball turns yellow. Boss-man wants a way to identify how far we are along with the aggregated Annual Objectives. Sorry if this is convoluted...I appreciate your thoughts!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…