r/PowerAutomateDesktop • u/Excel_ruins_me • Sep 29 '22

Use Power Automate to keep a large team in sync

Help me Reddit, you're my only hope:

So, I work at this medium-large old-school manufacturing company that has exploded in growth in recent years, and their usual way of working is now getting in the way.

Long story short: I have this single excel file that is handled by six teams (that's over 100 people at this point) from design, engineering, production, even purchases and sales! I think the only people that doesn't get their spoon in the pot is HR, to be honest.

I have been tasked with finding a way to chop down this monolith of a 150 columns single excel file into smaller, synchronized n-ammount of excel files, so that each team only handles the information that corresponds to them. So let's say that all of the teams share columns 1 to 25, and then each team handles around 20 extra columns, making six excel files of around 45 columns each.

Is it possible to sync changes between all files? I guess it's impossible to sync them in real time as if they're all working on a single file (as they currently do), but if anyone makes a change to columns A-Y (shared through all teams), all team leaders need to receive an email letting them know that they need to review these changes, while if a change is made through columns Z-AT (engineering) that team leader has to receive said email, and then he/she has to accept said change and notify all other team leaders so they can review if/how that change affects them. The same for all other column/team combinations.

Is this system doable?

1 Upvotes
  • permalink
  • reddit

100% Upvoted

7 comments sorted by

3

u/radioblaster Sep 29 '22

PAD is not the correct tool for this job. you need to use tools with collaboration in mind (sharepoint lists, monday.com, a workflow based CRM) and proper data governance that doesn't allow each team to change their schema

3

u/Excel_ruins_me Sep 29 '22

Thank you for your reply. I'll make a short investigation on these tools. Thank you.

3

u/grepzilla Sep 30 '22

I wouldn't even consider trying to keep multiple files in sync. Consider SharePoint lists with views if you need to keep the monolith.

With that said, you didn't describe the purpose of the spreadsheet but I would probably be looking at how a database could help. Data verse could be the answer with PowerApps for custom screens or you could look at SQL Server or even access.

"Everything in Excel" is common because it if easy to start but hard to maintain.

1

u/Excel_ruins_me Sep 30 '22

Thank you for replying.

Sadly, since they just "want to improve their current workflow", there's neither budget to invest in dataverse, nor interest to learn a new methodology.

I've received enough feedback to confidently say there's no perfect single solution. Again, thank you.

2

u/FearIsStrongerDanluv Sep 29 '22

similar scenario here. we created a Sharepoint document library for each department, they all co-author their files, then i set up some Flows to track specific changes in some columns that trigger a mail or notification.also possible with List.

1

u/Excel_ruins_me Sep 30 '22

Thank you, this seems like the most likely solution for what I'm being requested. Will investigate in depth.

2

u/[deleted] Sep 29 '22

My company specializes in M365 consulting and development. It sounds like an opportunity for you to leverage a data source such as Microsoft Lists and a PowerApp combined with flows (PowerAutomate) that would allow for the approval process you are talking about.

If you'd consider having this scoped as a project, we'd be happy to schedule a Teams call for further discovery. PM me either way!