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

briannalaroy

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!

Paul Newcome

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))), "")

Paul Newcome

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))), "")

briannalaroy

This is great, thank you @Paul Newcome!

A couple questions:

1. Do I need to manually set the number column always? I would like it to grab any new projects that are submitted along with their milestones.
2. How do I get it to cycle through all projects? While the formula above works great for the first project listed on the sheet, it's only bringing in that project

Thank you in advance!!

Paul Newcome

You would need to manually populate the numbers. You can populate extra sections to accommodate more projects being added in the future.

The reason it is only pulling in the first one is because you have a minor mistake in the number column. You don't repeat 1 1 2 3 4 over and over again. The first is 1 1 2 3 4. Then the second project is numbered 2 1 2 3 4. Third project is 3 1 2 3 4, so on and so forth as I have in my last comment.

briannalaroy

Oh I totally missed that - tired eyes.

THANK YOU, @Paul Newcome!!

Paul Newcome

Happy to help. 👍️