Give same value to parents, children, and grandchildren

MelissaYE

How can I make parents, children, and grandchildren show the same value for "Project Name" regardless of hierarchy level? I am importing some of the information in the sheet from the database and the children/grandchildren are subtasks that need to be labelled with the project name. The solutions I have tried so far have not worked.

KPH

Hi @MelissaYE

You can't create a column formula and also type into the same column. You could have a column formula that pulls in a value for the top parent level and the same for all children and grandchildren. But that value would need to be entered somewhere, and the somewhere cannot be the same column.

If you created another column (parent name, for example), and entered the Project name into that for all the top level rows, you could use a formula to return the value in Project Name column for the top level ancestor and then use the same for all children and grandchildren. This formula would do that.

=IF(COUNT(ANCESTORS(Task@row)) = 0, [Parent Name]@row, PARENT())

Is that what you are looking for?

KPH

If you are entering the Project Name in the first row in the hierarchy, then this formula in any of the child/grandchild rows will return the value from the parent.

=PARENT()

MelissaYE

@KPH I'd like to have a row that has the project name in all of the columns, including the parent row. Is there a way to create a column formula that gives that result?

Paul Newcome

Have you tried

="Project A"

and then setting it as a column formula?

MelissaYE

@Paul Newcome There is more than 1 project, Project A and Project B. I would like there to be a column that has all parents, children, and grandchildren to have the correct project name in a column. So all activities associated with Project A will have "Project A" in that column.

Paul Newcome

How are you establishing which project is which?

MelissaYE

@Paul Newcome the parent has the project name in the project column.

KPH

Hi @MelissaYE

You can't create a column formula and also type into the same column. You could have a column formula that pulls in a value for the top parent level and the same for all children and grandchildren. But that value would need to be entered somewhere, and the somewhere cannot be the same column.

If you created another column (parent name, for example), and entered the Project name into that for all the top level rows, you could use a formula to return the value in Project Name column for the top level ancestor and then use the same for all children and grandchildren. This formula would do that.

=IF(COUNT(ANCESTORS(Task@row)) = 0, [Parent Name]@row, PARENT())

Is that what you are looking for?

Paul Newcome

Are you able to provide a screenshot with data manually entered that shows what you are trying to do exactly?

Your initial screenshot isn't very clear based on the data that is in it in comparison to the hierarchy levels.

MelissaYE

@KPH Thank you so much! This does exactly what I want!

KPH

Wonderful!

I wasn't confident this was what you wanted so didn't explain the formula.

The formula is basically saying if there are 0 ancestors in the Task column, then use the value in the Parent Name column. If there are ancestors, then use the value in the current column in the parent row.