Need formula for identifying whether a task is a subtask (regardless of overall task level)

blawrence

Hello,

I have a project plan that has 6 levels and am looking for a formula that will identify, in a separate text column, whether or not a task is a subtask regardless of what level it is at. (So only that the subtask is a lower level than its parent task and not that it is at a specific level)

As an example, I would need to be able to automatically calculate in a report that there are 7 total subtasks (regardless of what level they are at).

• level 1
  • subtask
  • subtask
  • level 2
    • subtask
    • subtask
    • subtask
    • level 3
      • subtask
      • subtask

Thank you, in advance, for your help!

Joe Calderazzo

I do this by creating two columns named ANC and DES. These are helper columns that I use for the Conditions in Conditional Formatting.

For Column ANC, I enter the formula =COUNT(ANCESTORS()) and Convert that to a Column Formula (I also lock the column because I don't like others messing with my settings).

For Column DES, I enter the formula =COUNT(DESCENDANTS()) and convert that to a Column Formula (locking that column as well).

I shrink these down, center the data, and set to Grey background with Grey letters - no one needs to see them, so I hide these two columns as well.

Next is the magic, open Conditional Formatting and select Add New Rule.

Each level will require it's own rule, thus three levels requires three rules.

The first rule is the simplest: if ANC column is = 0, then apply X formatting to the entire row or just to the columns you need highlighted.

The second rule has one more step: if ANC column = 1 AND DES column Is Greater Than 0, then apply X formatting where needed.

The third and subsequent rules are much easier because you can Clone the second rule and just adjust the ANC = 2 and change the Formatting.

If fourth level is needed, then Clone the second rule and update ANC = 3 & update formatting, etc.

You can see where this goes….

I have a screen shot of the rules and the sheet below so you can see:

Hope this helps.

Andrée Starå

https://community.smartsheet.com/discussion/121150/need-formula-for-identifying-whether-a-task-is-a-subtask-regardless-of-overall-task-level

Hi,

I hope you're well and safe!

I usually add a helper column called Level with the formula below. You can then use it to identify parents, use it for conditional formatting, and more.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

I hope that helps!

Be safe, and have a fantastic weekend!

Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

blawrence

Joe & Andre - thank you both SO MUCH! I appreciate your time and expertise!

Best,

Brian

Andrée Starå

https://community.smartsheet.com/discussion/comment/433750#Comment_433750

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Joe Calderazzo

Glad we could help and give you options.