IF Formula inquiry

System

This discussion was created from comments split from: Report showing parent task?.

KBedal

I'm hoping that someone could offer a suggestion on a slightly more complicated version of this formula that I am trying to utilize. I have a project with up to 7 levels of hierarchy on a given task. I have tried both the =PARENT and the =JOIN(ANCESTOR) formats to display the parent tasks in the associated report. The problem I found was the PARENT function did not rollup into a high enough parent task to generate a detailed enough description of the task (for example returns "review" without any additional context), but the JOIN(ANCESTORS) rolled up to the highest level of parent task which was too much information for the report to display correctly, i.e.. 7 levels of tasks joined when only two or three would be perfect). I've gotten as far as the formula listed below to return the parent task one level above which is working for most cases, but I would ideally like to add in logic that will return the joined parent task names for the two levels above the task in question especially for the lowest levels of tasks. I have tried adding JOIN into the existing formula below, but I'm getting incorrect argument sets as the return. Any suggestions would be greatly appreciated!

=IF(level@row = 1, IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, INDEX(ANCESTORS([Task Name]@row), 1)), IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, INDEX(ANCESTORS([Task Name]@row), level@row - 1)))

SSFeatures

Hi @KBedal ,

I just created a mock sheet and tested out some formulas. This sheet has task names, a "Level" column, and a "Description" column that concatenates the task names based on the parent's task names.

This is the formula that I used for Level:

=COUNT(ANCESTORS())

This is the formula that I used for Description:

=IF(
    Level@row = 0,
    [Task Name]@row,
    IF(
        Level@row = 1,
        [Task Name]@row + " - " + PARENT([Task Name]@row),
        [Task Name]@row + " - " + PARENT([Task Name]@row) + " - " + INDEX(ANCESTORS([Task Name]@row), Level@row - 1)
    )
)

You can modify this formula to add more IF statements, or join more task names.

I hope this helps!