Help Needed: Creating more efficient ways to update sheets

henryling

Hello,



I am working on providing dashboards for my seniors. Here is the premise: I have multiple project managers (PMs) that work with four different agencies. Each PM has their own assigned sheet and assign an agency to the project. To create a dynamic dashboard, I am using a separate sheet (a table sheet) that pulls info from each of the PMs' sheets. I have dashes for each PM and agency. However, there is no one that updates the agency sheets (as the PMs update their own sheets).



To create the agency sheets, I basically filtered the agency from each PM and copied the rows into the designated agency sheet. However, this creates a static sheet that will not update with the PM sheet.



Does anyone know a more dynamic way to get information from the PM sheet? Reports don't work because I can't pull info from the report and quantify data. VLOOKUP won't work because the primary column that uses unique identifiers are spread throughout the different PM sheets.



The only other way I could think of was creating a report and exporting it to excel and importing it back into smartsheets every so often (maybe once a week) to keep it up to date. The problem with this approach is that the table sheet that is pulling info from all the PM and agency sheets uses SUMIF and COUNTIF formulas that require cross-sheet references. I'm afraid this might mean that every time I update an agency sheet I will have to update the reference to a new sheet. This might count towards the 100 cross-reference sheet limit per sheet (I am sitting at about 60 between all the PMs and agencies). Although, I was told that unused references are deleted automatically after two hours (not sure how true that is).



I know this was a long read. If anyone could help that would be great.



Thank you in advance

Andrée Starå

Hi Henry,

It sounds to me as something could probably be set up with cell linking.

Would that work?

Another option that could simplify the process would be to use the new premium add-on Data Uploader.

Please see the attached link/screenshot for more information.

https://www.smartsheet.com/datauploader

I hope this helps you!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting