Help Needed: Creating more efficient ways to update sheets

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