Combine data from parent/child rows into a single row

MrChris

Hi,

I would like to pull data from parent and child rows into a single row. I only want to display specific columns, so have been trying to do this using a report.

In the first column, I want to display the data from the parent cell. The other columns would have data from the child cells.

What I currently have is this:

What I would like to be able to view is this:

Where the final sheet/report does not require changes/input in the original sheet.

Is there any easy way for me to do this?

Thanks for your help!

Andrée Starå

Hi @MrChris

I hope you're well and safe!

You can convert it to a Column Formula, which will be automatically added to all current and new rows.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @MrChris

I hope you're well and safe!

Not sure I follow! Can you elaborate?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MrChris

Hi Andrée,

Thanks for getting back to me. I will try and elaborate further on what we want to achieve.

In our group, team members report and comment on project progress each week using MS Word documents. Currently, information like the project number, status, start date and end date are input manually. We want them to be able to copy/paste directly from Smartsheet into the document. This needs to be as easy and quick as possible to reduce admin burden.

We have a Smartsheet that we use to keep track of projects. Each project is allocated one parent row. This will include the project number in one of the cells (“Project 1”). There is additional information in other cells in the parent row.

Each project parent row also has between 1-5 children rows with additional information. Each row represents a stage of the project, so the number of stages (rows) depends on the scope of the project. Each stage is linked to a particular person in the team in the “Assigned to” column. Sometimes one person is allocated to more than one row.

One person is assigned to the project overall in the parent row, and they may also be assigned one of the stages in a child row (in the example below, “Person 2” is assigned to the project overall in the parent row, and also to Stage 2 in one of the child rows).

What we want to be able to do is display part of the information in the parent row (the project number) and part of the information in the children rows (Stages, Status, Start Date and End Date) together in the same row. We then want to filter the rows based on both the person assigned in the child rows and the status of a project.

Example: Person 1 wants to view all project stages that they are assigned to and which have a status of In Progress. They want to view the Project No., Stages, Status, Start Date and End Date for these projects. Each project would display information like the image below (but excluding the “Assigned To” column). They can then easily copy key information about the project and paste into the separate document to report on progress for the week.

To be clear, we want to be able to do this without making any changes to the original sheet where all this information is captured.

I hope this clarifies what we want to be able to do.

Thanks!

MrChris

Does anyone in the community have any ideas how this could be achieved? Happy to provide further details if required.

Thanks

Paul Newcome

The only way to accomplish this is to make some adjustments to the original sheet.

MrChris

Hi Paul,

Thanks for your response. Could you please let me know what (ideally minimal) changes to the original sheet would be required to make this work?

Thanks!

Paul Newcome

My suggestion would be to use formulas in helper columns (they can be hidden to keep the sheet looking clean) to pull the parent row data down onto each of the child rows. From there I would create a report that is filtered by user.

MrChris

Hi Paul, thanks for your comment. Is it possible to do that to only pull down one or two cells of data from the parent row into each child row, but not every cell? Could you please elaborate how you would do that?

We have a number of sheets/dashboards which pull data directly from this particular sheet (let's call it Sheet 1). There is a possibility that making these changes will disrupt some formulas, which would be very difficult/convoluted to resolve.

In the event that this happens, would it be possible to somehow mirror Sheet 1 into a new sheet (let's call it Sheet 2), within which the changes could be made without affecting the original (Sheet 1) and the linked sheets/dashboards?

Thanks!

Paul Newcome

You would insert a new column and use something like this

=PARENT([Column Name]@row)

MrChris

Hi Paul,

Thanks for the formula.

Is there any way to automatically populate a formula like that into any new child rows, for a given column? Or can it be automatically populated into any new rows that are inserted, for a given column?

Thanks

Andrée Starå

Hi @MrChris

I hope you're well and safe!

You can convert it to a Column Formula, which will be automatically added to all current and new rows.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MrChris

Hi Andrée, this is perfect, thank you!

Andrée Starå

@MrChris

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!