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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.