Need help with getting parent and sub parent data

Bill Brandt

I have a format that looks like this

Project Name

      PreCon

              Name

              Name

       Construction

               Name

I am looking for a formula that adds text to a column for each name structured as "Project Name - PreCon" or "Project Name - Construction"

I can get it to work using explicit references

=PARENT([Task Name]13) + " " + PARENT([Task Name]14)

This only works for the first Name row and then I need to edit each subsequent Name row. Can this formula be edited to always pull in the "Project Name" and "PreCon/Con" info?

 

 

Adam Overton

I'm not 100% sure I got your question right, but I think you want something like this:

=JOIN(ANCESTORS([Task Name]3), " - ") + ": " + [Task Name]3

 

J. Craig Williams

Bill,

This might be what you are looking for:

=IF(COUNT(ANCESTORS([Task Name]23)) = 0, [Task Name]23, IF(COUNT(ANCESTORS([Task Name]23)) = 1, PARENT([Task Name]23) + " - " + [Task Name]23, [MyColumn]22))

for row 23.

I used [MyColumn] for the column that has the formula.

It should not be copied to row 1, as it references the row above itself when it does not find a parent or grand-parent hierarchy.

For the Project Name row, it will display "Project Name" (no hyphen)

I hope this is what you were looking for.

Craig