Linking to External spreadsheets

[karenynp]

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 summary data to my master spreadsheet.

What is the best way to pull the data from the summary tabs into my master spreadsheet?  Cutting and pasting will take quite a while.

Can I link to that many external spreadsheets or is that beyond the capacity of excel?

[#2]

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/

[#3]

@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!

[#4]

@karenynp 

My pleasure!