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:
Best 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 multipledigit 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

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 multipledigit 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
Categories
Check out the Formula Handbook template!