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:

Find more posts tagged with

Formulas

Accepted answers

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! 😊

All comments

Tamara Purchase

Hi Julie,

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.