Predecessors and delete rows

Hello, is there any way to tell if a line is the predecessor of another line? For example, if line 98 has line 5 as its predecessor, is there a way to add a column with a formula that indicates that line 5 is linked to a predecessor? All our projects are created from a template with predecessors and if we delete line 5 because it's not useful in the project, we can't know that it has an impact on line 98 and the person responsible for this line sees a #ref in the predecessors column and doesn't know what it depended on in the first place. with a column indicating that this line is the predecessor of one or more other lines, the user would know that he or she could not delete the line without first validating the impacts.

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓

    Hi @Julie Barbeau

    First, your question was hard to read, so I quoted it here.

    '''Hello, is there any way to tell if a line is the predecessor of another line? For example, if line 98 has line 5 as its predecessor, is there a way to add a column with a formula that indicates that line 5 is linked to a predecessor? All our projects are created from a template with predecessors and if we delete line 5 because it's not useful in the project, we can't know that it has an impact on line 98 and the person responsible for this line sees a #ref in the predecessors column and doesn't know what it depended on in the first place. with a column indicating that this line is the predecessor of one or more other lines, the user would know that he or she could not delete the line without first validating the impacts.'''

    A short answer to your question is to use the SUCCESSORS function.

    https://help.smartsheet.com/function/successors

    = JOIN(SUCCESSORS(Column@row),”,”)

    In my demo, [[Successors #]

    =JOIN(SUCCESSORS([Task Name]@row), ", ")

    https://app.smartsheet.com/b/publish?EQBCT=8feda050cf5a4661add350961546f993

    We need to use JOIN or some other function that takes a range as an argument, as the SUCCESSORS function returns just a range of task row numbers.

    So, if you want to find the number of a given task's successors, the formula would be like this;

    [No of Successors]

    =COUNT(SUCCESSORS([Task Name]@row))

    With that information, you can flag the task as shown in the image of the demo solution below.

    [Has Succusessors]

    =IF(ISBLANK([Successors #]@row), 0, 1)

    If you want to get the individual row number of successors, you can use the INDEX formula like this;

    [SUC Row # 1]

    =IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1, 1), "")

    [SUC Row # 2]

    =IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1, 2), "")

    As I found, the SUCCESSORS function seems to return a range with one row with several columns. So, the INDEX function needs to change the column position like INDEX({Successor Range}, 1, relative column position like 1, 2, 3).

    Using the individual row number of successors, you can get the Task Name of successors using the INDEX function like the following;

    [SUC 1]

    =IF(ISBLANK([SUC Row # 1]@row), "", INDEX([Task Name]:[Task Name], VALUE([SUC Row # 1]@row)))

    [SUC 2]

    =IF(ISBLANK([SUC Row # 2]@row), "", INDEX([Task Name]:[Task Name], VALUE([SUC Row # 2]@row)))

    Finally, using all the information above, my demo solution displays the [Task and Successors Tasks] as the display label for the gannt bars.

    [Task and Successors Tasks]

    =IF([Has Successors]@row, [Task Name]@row + IF(ISTEXT([SUC 1]@row), " > " + JOIN([SUC 1]@row:[SUC 3]@row, " ")))

    I hope the demo solution helps you solve your challenge.😀

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓

    Hi @Julie Barbeau

    First, your question was hard to read, so I quoted it here.

    '''Hello, is there any way to tell if a line is the predecessor of another line? For example, if line 98 has line 5 as its predecessor, is there a way to add a column with a formula that indicates that line 5 is linked to a predecessor? All our projects are created from a template with predecessors and if we delete line 5 because it's not useful in the project, we can't know that it has an impact on line 98 and the person responsible for this line sees a #ref in the predecessors column and doesn't know what it depended on in the first place. with a column indicating that this line is the predecessor of one or more other lines, the user would know that he or she could not delete the line without first validating the impacts.'''

    A short answer to your question is to use the SUCCESSORS function.

    https://help.smartsheet.com/function/successors

    = JOIN(SUCCESSORS(Column@row),”,”)

    In my demo, [[Successors #]

    =JOIN(SUCCESSORS([Task Name]@row), ", ")

    https://app.smartsheet.com/b/publish?EQBCT=8feda050cf5a4661add350961546f993

    We need to use JOIN or some other function that takes a range as an argument, as the SUCCESSORS function returns just a range of task row numbers.

    So, if you want to find the number of a given task's successors, the formula would be like this;

    [No of Successors]

    =COUNT(SUCCESSORS([Task Name]@row))

    With that information, you can flag the task as shown in the image of the demo solution below.

    [Has Succusessors]

    =IF(ISBLANK([Successors #]@row), 0, 1)

    If you want to get the individual row number of successors, you can use the INDEX formula like this;

    [SUC Row # 1]

    =IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1, 1), "")

    [SUC Row # 2]

    =IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1, 2), "")

    As I found, the SUCCESSORS function seems to return a range with one row with several columns. So, the INDEX function needs to change the column position like INDEX({Successor Range}, 1, relative column position like 1, 2, 3).

    Using the individual row number of successors, you can get the Task Name of successors using the INDEX function like the following;

    [SUC 1]

    =IF(ISBLANK([SUC Row # 1]@row), "", INDEX([Task Name]:[Task Name], VALUE([SUC Row # 1]@row)))

    [SUC 2]

    =IF(ISBLANK([SUC Row # 2]@row), "", INDEX([Task Name]:[Task Name], VALUE([SUC Row # 2]@row)))

    Finally, using all the information above, my demo solution displays the [Task and Successors Tasks] as the display label for the gannt bars.

    [Task and Successors Tasks]

    =IF([Has Successors]@row, [Task Name]@row + IF(ISTEXT([SUC 1]@row), " > " + JOIN([SUC 1]@row:[SUC 3]@row, " ")))

    I hope the demo solution helps you solve your challenge.😀

  • Julie Barbeau
    Julie Barbeau ✭✭✭✭

    @jmyzk_cloudsmart_jp thank you so much!!! it works perfectly! and sorry for my english, it is not my first language :)

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Salut @Julie Barbeau

    Le français est votre langue maternelle ? Je suis japonais. Je suis là pour vous aider ! Si vous avez trouvé cela utile, veuillez cliquer sur l'une des options suivantes : Perspicace, Voter pour, Génial. 💗 (Deepl)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!