Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Need help with getting parent and sub parent data

Options
Bill Brandt
Bill Brandt ✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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?

 

 

Tags:

Comments

  • Adam Overton
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

This discussion has been closed.