Fill in project number based on ancestor level

Julie Fleming

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:

Tamara Purchase

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!

Tamara Purchase

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!

Julie Fleming

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))

Julie Fleming

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!