I am looking for an automated solution to update One excel workbook on a weekly basis from 31 Microsoft Teams weekly workbooks.


Each team has four members, a captain and three members. Each week a new workbook is created with 5 worksheets. The first worksheet is for Team Points. It gets daily totals for each member and then a combined team total for the week. The remaining four sheets are identical in design where it tallies daily points and automatically calculates the daily total as it also automatically accumulates a total for the week. The calculated totals from each member sheet are automatically updating the Team Points sheet in summary form via internal referencing. Outside of Microsoft Teams I have been able to use an external cell reference to update calculated totals but I'm trying to automate this and say all the work with 31 workbooks every week.

I know how to do this outside of Microsoft Teams but the whole purpose of the MS Teams was so that we didn't have to get IS involved in setting up shared folders. Also, we only want each team to see their totals and not other treams totals.

The program coordinator has owner permissions to all 31 teams.

I'm trying to look for a simple and quick way for the program coordinator to get or have all 31 weekly team points so she can create an email to send out to everyone on the top three teams for the week.

We are on O365 or Excel 2016 whichever when they open the excel file in ms teams, they hit the edit button that edits online I believe.

Do you have any idea of any solution to try to make the coordinators job more automated? Thanks so much for your creative help in advance!!!

Sincerely, Michelle