Fill in project number based on ancestor level

I want to auto fill the project_number between the the Purple and Red rows. So all the children of Purple Project have a Project_Number = 4 and all the Red Children have a Project_number of 7

Tried using something like this. So if the Project is checked return the Project_Number. This give me "UNPARSABLE"

=INDEX(COLLECT(Project_Number:ProjectNumber,Project:Project,1),1)

Tried to use ANCESTOR.

Help... TIA!

Sample data:


Tags:

Best Answer

  • Tamara Purchase
    Tamara Purchase ✭✭✭
    Answer ✓

    Hi Julie,

    If your primary column is always going to have the text "#..." at the end of the field for the first parent row in each group, you should be able to use the "#" as a starting point to reference in a RIGHT function. In this case, the below formula should work as a column formula in the Project_Number column. You will just need to substitute your column name "Project\Epic\Feature\Sprint Objectives\Task" wherever it says Primary Column.

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND("#", [Primary Column]@row)), PARENT([Project_Number]@row))

    Using the LEN - FIND functions should keep the formula working correctly as you get into multiple-digit project numbers. Keep in mind that this formula will need to be updated if the text "#..." is ever omitted/changed in the primary column or if it is not the rightmost text in the field.

    Hope this works for you! 😊

Answers

  • Tamara Purchase
    Tamara Purchase ✭✭✭
    Answer ✓

    Hi Julie,

    If your primary column is always going to have the text "#..." at the end of the field for the first parent row in each group, you should be able to use the "#" as a starting point to reference in a RIGHT function. In this case, the below formula should work as a column formula in the Project_Number column. You will just need to substitute your column name "Project\Epic\Feature\Sprint Objectives\Task" wherever it says Primary Column.

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND("#", [Primary Column]@row)), PARENT([Project_Number]@row))

    Using the LEN - FIND functions should keep the formula working correctly as you get into multiple-digit project numbers. Keep in mind that this formula will need to be updated if the text "#..." is ever omitted/changed in the primary column or if it is not the rightmost text in the field.

    Hope this works for you! 😊

  • Thank you! I like this idea. However, it gives me unparasable 😔

    =IF(COUNT(ANCESTORS([Project\Epic\Feature\Sprint Objectives\Task]@row)) = 0, RIGHT([Project\Epic\Feature\Sprint Objectives\Task]@row, LEN([Project\Epic\Feature\Sprint Objectives\Task]@row) - FIND("#", [Project\Epic\Feature\Sprint Objectives\Task]@row)), PARENT([Project_Number]@row))

  • I got it!

    Did a different approach but it worked!

    Based the formula off a helper column with the level and added a Schedule column.

    So i only have to put the Project_Number in a column on the row that is colored.

    =IF([Level_help]@row = "1", [Project Number]@row, IF([Level_help]@row = "2", PARENT([Project Number]@row), IF([Level_help]@row = "3", PARENT(Schedule@row), PARENT(Schedule@row))))

    THANK YOU AGAIN FOR THE HELP!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!