Want a column to autopopulate parent taskname

Corey McCarren

As above. For reporting purposes, I want a column where all children rows automatically populate the parent row task name.

Secondarily, if possible, I would also like a column where if a child is indented once, the column has one value (in this case, "Method"). If the child is two indents, the column auto-populates to "Metric"

Appreciate any help!

Kelly Moore

Hey @Corey McCarren

You'll need 2-3 helper columns to accomplish the above. If I wasn't worried about the number of columns in a sheet, I would use 3 columns so I could keep the 'level' formula in a column all of it's own.

Helper #1. I typically call this column 'Level', as will list the hierarchical level of the row. It's very straight forward if all of your rows are either a parent, grandparent or a child. If you have just plain rows we'll have to modify the formula slightly. We'll assume for now its the simplest case.

Level = COUNT(ANCESTORS())

You''ll see your different indentations will have different Levels of hierarchy.

To get the name of the Parents in a column

Helper#2

=IF(COUNT(CHILDREN())>0,[your primary column]@row, PARENT())

*you'll have to modify the formula to insert the real name of your primary column.

Helper#3

Take note of the different levels of your children with only a single indent and those with the double indentation. For the sake of the formula I will assume the single indent is level 1, and the double is level 2. If this is different, you will need to modify the formula accordingly.

=IF(Level@row =1, "Method", IF(Level@row=2, "Metric"))

Will these work for you?

Kelly

Corey McCarren

@Kelly Moore Thank you so much! I have gotten Helper 1 and Helper 3 to work.

However for Helper 2:

=IF(COUNT(CHILDREN())>0,[Task Name]@row, PARENT())

I am getting either #CIRCULAR REFERENCE or #BLOCKED errors

Corey McCarren

Solved I believe!

The formula =PARENT([Task Name]2) works

2 being the same row as the formula is in. Ideally I would edit the formula to be blank if it there are less than X number of parents or ancestors, although I could just filter out the irrelevant parents in my report

Kelly Moore

Hey @Corey McCarren

Good, I'm glad you figured out to insert your primary column. Instead of using the specific row number, use @row so that it remains a dynamic reference.

=PARENT([Task Name]@row)