Forum Discussion

karenynp's avatar
karenynp
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 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?

excel

3 Replies

  • PReagan's avatar
    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's avatar
      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!

Resources