Forum Discussion

Copper Contributor

Aug 20, 2020

Linking to External spreadsheets

Expert advice please! I need to pull data from 130 different spreadsheets. I need to go into each spreadsheet (each have 40 tabs) - add a tab to summarize the data in each sheet and then pull that ...

excel

PReagan

Bronze Contributor

Aug 20, 2020

Hello karenynp,

I'm sure that I am oversimplifying this a bit here but to start, you could sum the necessary data in a summary worksheet for each workbook by using a formula such as:

=SUM(Sheet1:SheetX!A1)

Once completed for each of the workbooks, it would be possible to use Power Query to compile the data from all 130 workbooks into a single master workbook:

https://www.excel-university.com/retrieve-values-from-many-workbooks/

karenynp
Copper Contributor
Aug 20, 2020
PReagan thank you. I knew how to build that formula, but Power Query is something I will need to learn. Sounds like it will do the trick!
- PReagan
  Bronze Contributor
  Aug 25, 2020
  karenynp
  
  My pleasure!