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.