Referencing Level 1 Cell Value to a new column

JPhillips

I am looking to make a helper column that copies the value from the Level 1 Parent in my project sheet. If there are several children how do I reference the Level 1 value to copy that value in the entire helper column?

First project section:

Level 1 "Report Execution"

Level 2, 3, 4, children

Second Project section:

Level 1 "Data Clean"

Level 2, 3, 4 children

• Helper column copies the Level 1 value from each respective project section.

Thank you.

Brad Klodowski

I'll usually add a Hierarchy Level column as a helper (you can hide afterward) that simply counts the ancestors of the given row's primary column. So you end up with a level 0 (top level), level 1 (1 under that) and so forth. I usually use this value to drive custom formatting, too.

The helper column you are looking to add would need to contain the task name if that hierarchy level was 0, and the value of its parent otherwise.

Something along the lines of: =IF([Hierarchy Level]@row = 0, [Task Name]@row, PARENT()) - what this will do is propagate the task name down through all children of that first row where you set the helper equal to the task name. Of course you can do the math inline if you have no other need for the helper column, which is to say, just check if the count of Ancestors is 0 as the condition in your formula instead of referring to the helper column, like: =IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, PARENT()).

Here is how that looks:

Hope this is helpful! Please feel free to let me know if I need to explain further.

JPhillips

Like this:

Brad Klodowski

I'll usually add a Hierarchy Level column as a helper (you can hide afterward) that simply counts the ancestors of the given row's primary column. So you end up with a level 0 (top level), level 1 (1 under that) and so forth. I usually use this value to drive custom formatting, too.

The helper column you are looking to add would need to contain the task name if that hierarchy level was 0, and the value of its parent otherwise.

Something along the lines of: =IF([Hierarchy Level]@row = 0, [Task Name]@row, PARENT()) - what this will do is propagate the task name down through all children of that first row where you set the helper equal to the task name. Of course you can do the math inline if you have no other need for the helper column, which is to say, just check if the count of Ancestors is 0 as the condition in your formula instead of referring to the helper column, like: =IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, PARENT()).

Here is how that looks:

Hope this is helpful! Please feel free to let me know if I need to explain further.

JPhillips

Brad, that worked exactly as I wanted. Thank you!