Hierarchy tagging

Jeremy_Busch

Greetings, 

 

I have a organizational structure that has three levels

• Campaign
• Project
• Deliverable
• Task

I am trying to figure out a way how I can have a column with one of the above values that is based on hierarchy. I have the project and deliverable part figured out using the following formula:

=IF(COUNT(CHILDREN(Description7)) > 0, "Project", IF(COUNT(CHILDREN(Description7)) = 0, "Deliverable"))

Is there a way to expand this formula to take into account Campaign and Task row types? 

Thanks,

Jeremy

Paul Newcome

Try using an

 

=COUNT(ANCESTORS(Description@row))

 

for each row in a separate column (We'll call it "Helper" for this example).

 

You'll then get a number based off of the level of hierarchy with 0 being the "Parent Parent" row (child of no rows at all).

 

You can then build a table sort of like this (column headers in bold):

Type                       Level

Campaign               0

Project                    1

Deliverable              2

Task                        3

 

You can then enter the following formula into your column where you want to house the row tag:

 

=INDEX(Type:Type, MATCH(Helper@row, Level:Level, 0))

 

This will take the number from your Helper column and pull the appropriate value from the Type column of your table.

 

You COULD just write out a nested IF statement to cover each of the levels and do away with the table, but if you have any changes at all later down the road, that means having to get into what could end up being a rather long formula and hoping you don't have any typos and whatnot.

 

With the INDEX/MATCH method hitting up against the table, you have very easy flexibility to add new levels, remove levels, and change the level names without having to edit the formula itself.

 

One thing I have found through personal experience is that sometimes having a few extra steps and columns in the building process makes the long run SO much easier. Especially since those extra columns can just be hidden when you're done to keep the sheet looking clean.