Auto-populate parent/child rows in a separate sheet?

Question

Hello! I was wondering if there was a way to have certain column data automatically transferred to another sheet. Once that data is transferred, I would like for that transferred data to automatically populate into an existing parent row (if the account name matches).

Example: Sheet 1 is a request sheet that is auto filled from a form. Once the request is complete I would like it to transfer only certain columns to Sheet 2.

Sheet 2 is an internal record keeping sheet. If the Account name from Sheet 1 already exist in Sheet 2 but the project is different, I would like that data to show up on sheet 2 as a child row. That way I can view all projects (child rows) under each Account name (parent row).

Is this possible?

ccarriger · Answer

@Kbrown15 are your project names unique between accounts? So say Account A and Account B might have similar projects but their projects would never have the same name as each other?

If so, on Sheet 2 you could use an Index/Distinct formula like the one below. That would run off your project column and get the unique instances of each project name. And then I would use Index/Collect formulas to populate the Account and other columns you want that live in Sheet 1 over to Sheet 2.

INDEX(DISTINCT(COLLECT({Project Name column from Sheet 1}, {Project Name column from Sheet 1}, @cell <> "")), Index@row)

Paul Newcome · Answer

I would start by looking into a grouped Row Report. That will allow you to gather each row from your sheet and group them by account. You can use the Summarize feature to get various totals, and you can also limit which columns are shown.

Otherwise you will need to leverage the API, the premium add-on Bridge, or some other similar third party app.

Adrien Leduc · Answer

To do that cleanly, you would need the "Update row" function in Bridge.

Another way to do something that "feels" like it is to use reports and Grouping & Summary:

https://help.smartsheet.com/learning-track/best-practice-webinars/reports