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 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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!