What is the best way to update data between two sheets w/o having to do it manually?

lora.riggs · 12/30/22

I have a Project Tracking Sheet for all of my team's projects. One of our repeat projects is rollout of the inventory module in our company's primary software. Since we are a shared services group owned by a PE firm, the processes around this implementation are always the same, we just have to track each separately since all of the companies in our scope operate independently and in separate tenants within the software system. All this to say: each inventory project has to live on a separate sheet so that we can grant access to different people on each project.

I have figured out how to get each Inventory Project Sheet to copy to my main Project Tracking Sheet. (I have a template with an automated workflow set up to copy rows to the Project Tracking Sheet when the Assignee column is changed on the Inventory Project Sheet. Each time a new inventory project starts, I save the template as a new file, then update the Assignee, at which point the rows copy to the main Project Tracking Sheet.) I know that updates to one sheet do not flow to the other, but I read that I can use a report to update multiple sheets at once.

I am struggling with a row report. I have a single parent row for each of these projects with multiple child rows, but it seems that only my parent rows live in the report. I read a little about creating a helper column around hierarchy for this, but I am even approaching this the right way to begin with? Is a row report the way to be able to update both sheets? Is there a form that would do something similar/better?

Andrée Starå · 01/02/23

Hi @lora.riggs

I hope you're well and safe!

Not sure I follow. Can't you get all rows showing in the Report?

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Have a fantastic week & Happy New Year!

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!

lora.riggs · 01/06/23

https://community.smartsheet.com/discussion/comment/355648#Comment_355648

Here is a screenshot of one of the sheets.

And then the report.

The sheet in the first screenshot correlates to the Sheet Name in Row 2 of the report.

MichaelTCA · 01/06/23

Hello,

The best way to update data depends on the license or "additions" you purchase (like Data Shuttle). There's a few add-ons that are beneficial to your questions, but only bits and pieces of each would apply to the one thing you are trying to do.

I use Data Shuttle and you can set a schedule to automatically update info. Cross-Reference functions also help reworking data from what you have imported, so multiple locations will be updated from 1 scheduled import. You will need the data to be sourced in a cloud server, such as OneDrive.

The reports are developed by the sheet, column, and sheet formatting. Even if there is a parent/child hierarchy.

You will need a separate column for each sub task if you want to be able to filter/sort or change data within the report per sub task.

Yes, changing data in the report also changes what is in the sheet. BUT, depending on the permissions of the user, the column cannot be locked, cannot have a function, and cannot be auto-numbering.

Forms require individual columns for each input of data, just like reports. Great for creating new rows.

You would need a column for the parent and column(s) for any additional "child" inputs.

You can enter multiple tasks into a form, but it will only show up in 1 cell unless there is a separate column for each entry.

There are no functions or automations, at this point, to automatically develop a parent/child hierarchy either, which would be nice to be able to convert it easily back and forth. The CHILDREN/PARENT functions are more for evaluating data instead of reformatting. I would try to stay away from a hierarchy if possible. Great for organization, dashboards, presentations, meetings, or references, but it doesn't offer all of the same features as using individual columns. (Sadly)

lora.riggs · 01/10/23

https://community.smartsheet.com/discussion/comment/356299#Comment_356299

I appreciate the input, but the hierarchy structure is necessary to maintain organization for the sheer number of projects we will be managing. Also I am not advanced enough yet to use all of the other recommendations you posted and need a quick fix for managing my team's projects until I can get up to speed on all the other functionalities you mentioned.

MichaelTCA · 01/10/23

@lora.riggs Anytime!

You mentioned having a file for each project. Make a folder for ONLY these project files or you'll have to individually select which files you want in the report. Select this entire folder as the data in the report, so that it adds files to the report as new ones are created (instead of manually selecting each new one).

Make sure you add the Sheet Name column to the report.

Group the data based on the Sheet Name.

If you notice in this example below that the sheet has parent rows and the report does not. It is because of a filter. Each task has someone assigned to it, but no one is assigned the parent, so if I filter by that column, the parent rows won't show in the report.

Also, if you don't like having the Sheet Name column showing (like me), hide it.

Sheet: (Each Individual Project File)

AND THE

Report:

What is the best way to update data between two sheets w/o having to do it manually?

Answers

Categories