Column data moved to another sheet (parent/child relationship)

Hi, I am fairly new to Smartsheet and looking to create a sheet to track milestone progress for strategic projects we have here.

Project Intake sheet looks like this: 


I would like to take only the Strategic project types, create the Initiative Name as the parent and all milestones listed as children (there are 4 milestones per project).

Milestone Tracking sheet idea:


 Is there a way for me to take the column data and create rows in this manner?

I have been reading other discussions that refer to a helper sheet, but struggling to get this going in the way I want!

Thank you!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need a helper column on the target sheet (called "Number" in this example) that is manually populated with some numbers (structure below) and already indented to show the parent/child relationships.

    1

    1

    2

    3

    4

    2

    1

    2

    3

    4

    3

    1

    2

    3

    4

    Then in the Primary Column of the source sheet you would use a column formula of

    =IFERROR(INDEX(COLLECT(IF(COUNT(CHILDREN(Number@row))> 0, {Initiative Name}, IF(Number@row = 1, {MIlestone 1}, IF(Number@row = 2, {Milestone 2}, IF(Number@row = 3, {Milestone 3}, {Milestone 4})))), {Project Type}, @cell = "Strategic"), IF(COUNT(CHILDREN(Number@row))> 0, Number@row, PARENT(Number@row))), "")

Answers