Dashboard - need to pull data from multiple feeder sheets

Hi guys,

Looking for advice on how to pull the data from say 7 separate workbooks into one centralised spreadsheet. Each spreadsheet represents a different data set. So one would show how long someone worked that day, how long their breaks were, how long their lunch etc. Another would show how long they spend on calls. Another the number of cases they closed that day. You can see what I’m getting at right?

Now these separate spreadsheets are all set up where new daily data is added to a table of data, which is then put through a pivot to produce the numbers the bosses want. They are updated daily. Now they want to pull all the different sets of data together onto yet another workbook so they can then have visibility over what staff are doing in all aspects of their job and to then use this to work out efficiency etc in order to performance manage better.

The person before me was manually copying and pasting the data over. That is a ridiculous waste of time. What is the best way to pull all this data together quickly and efficiently?

Tried VLOOKUPS but because it is over so many spreadsheets and different days I am spending a lot of time manually changing cell references within the formulas for each column. I need something quicker and more dynamic that once I have a formula in for the cell/column it will pull the data through by Date, Team, Person for each data set.

I’ve asked various colleagues all with vague advice. Index/Match, Index/Match with INDIRECT function, using Named Ranges, VBA….the Index/Match did look quite promising but most of the guides I’ve seen relate to sheets within the same workbook.

If you can highlight any pitfalls or well known issues I would appreciate it too.

Thanks a lot in advance

Yeah I am using Excel 2016 but have never actually used PowerQuery. Is it relatively easy to use? Would I need like a Unique Identifier shared amongst all of the spreadsheets in order to "join" the data or will it automatically pull all data for one person per day and list them in the order I need?

It sounds like it has real possibilities but I am a bit hesitant never having used it before and given the feeder spreadsheets are already set up I would prefer not to have to start from scratch and end up messing it up. If i can find a formula based method I can get that sorted and then go away and produce a test version using PowerQuery. I can then run both in tandem for a number of weeks to ensure I have set it up correctly and compare the data.