Mar 31 2021
- last edited on
Feb 01 2023
I don't know whether this is the right space to post this question, but I haven't found a better place.
Here is my problem statement:
I need to collect inputs from a variable number of people through an Excel spreadsheet. Each person will get an individual Excel file to fill in the requested data. The structure of this file is the same for all because it is based on a template. Basically, there is a table that people should populate.
After people returned the Excel files, they are stored in a Sharepoint folder (with arbitrary filenames!).
Now, I would like to collect the data from all files in that Sharepoint folder and copy it into one single table in a separate Excel master spreadsheet automatically.
The master spreadsheet is used to analyze the data, run statistics, create dashboards etc.
How would you do this?
Thanks for your suggestions.
Apr 01 2021 02:47 AM - edited Apr 01 2021 03:22 AM
1) If you're using SharePoint then you could just use one spreadsheet and "share" it to those that need to populate it and they can individually go in and update it.
2) If that doesn't work for you then try using Power Automate Desktop (https://flow.microsoft.com/en-us/blog/power-automate-desktop-march-2021-updates/) to pull the data together once you have it on your machine (or even use Power Automate/FLOW).
3) Or take a look at YouTube https://www.youtube.com/watch?v=dse9h2W4ejw (is one example) for a manual solution.
4) Final suggestion: Use Microsoft Forms. Create a Form to capture the data you need. Once all forms have been submitted go back in to Forms, go to "responses" and click "Open in Excel". All the replies will be in a spreadsheet. Manipulate it, save to SharePoint. Very little work required.
Apr 01 2021 07:10 AM
Apr 04 2021 07:15 AM
Apr 06 2021 06:18 AMSolution