Automatically transferring data from multiple new spreadsheets with fixed source formatting to master spreadsheet

Question

I'm part of an organization that has spreadsheet templates that individuals can fill out, input with their own unique details, and resubmit for processing. I'm trying to come up with an efficient way to transfer relevant details from these individual spreadsheets and upload it into a master spreadsheet. Unlike other cross-spreadsheet methods that work with a fixed number of spreadsheets, the number of spreadsheets being processed changes month-to-month, along with the individuals submitting them.

Each spreadsheet submitted follows the same format—I know where, on each sheet, the information I want is located—but it's split between multiple tabs and different columns and rows. Each spreadsheet is titled in a similar way, except for the unique individual characteristics.

Since I only want to transfer a collection of single cells from these individual spreadsheets to the master list, is there an easy way to create a set of formulas that reference the cells I want to capture—which again, are constant across all spreadsheets—but can be changed slightly to populate the data from new distinct spreadsheets as they're added?

Any help would be much appreciated.

Answer 1

Hi,

Follow the steps shown in this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks (youtube.com)

Answer 2

Hi Ben

In my opinion, Power Query might be the best approach to finding a solution to your problem.

The video below will guide you,

Do let me know if you need more help during the process.

Regards

Jeovany

https://www.youtube.com/watch?v=sLW3NbeGDy8&t=419s&pp=ygUfcG93ZXIgcXVlcnkgZnJvbSBtdWx0aXBsZSBmaWxlcw%3D%3D