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
-
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
-
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))), "")
-
This is great, thank you @Paul Newcome!
A couple questions:
- 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.
- 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!!
-
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.
-
Oh I totally missed that - tired eyes.
THANK YOU, @Paul Newcome!!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives