Results 1 to 3 of 3

Thread: Multiple Worksheet Consolidation

  1. #1

    Multiple Worksheet Consolidation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a task that I am trying to accomplish using Excel 2013, but I cannot seem to figure it out...

    I have multiple spreadsheets, all with the same column headings, and I am trying to get all of the data from the three spreadsheets to show in a single master spreadsheet. And I want the master spreadsheet to dynamically update based on the data put into any one of the three spreadsheets. Below is an example:

    Spreadsheet1
    Col1
    Col2
    Col3
    dataA1 dataA2 dataA3
    dataB1 dataB2 dataB3
    dataC1 dataC2 dataC3

    Spreadsheet2
    Col1
    Col2
    Col3
    dataD1 dataD2 dataD3
    dataE1 dataE2 dataE3
    dataF1 dataF2 dataF3

    Spreadsheet3
    Col1
    Col2
    Col3
    dataG1 dataG2 dataG3
    dataH1 dataH2 dataH3
    dataI1 dataI2 dataI3


    This is what I want the master to look like, and I want it to be able to dynamically update based on values put into any one of the three tables above. This master table will not be manually edited after it is created, and could even be read only. It should automagically update when it is opened based on the data in the other spreadsheets.

    Master Spreadsheet
    Col1
    Col2
    Col3
    dataA1 dataA2 dataA3
    dataB1 dataB2 dataB3
    dataC1 dataC2 dataC3
    dataD1 dataD2 dataD3
    dataE1 dataE2 dataE3
    dataF1 dataF2 dataF3
    dataG1 dataG2 dataG3
    dataH1 dataH2 dataH3
    dataI1 dataI2 dataI3


    How can I accomplish this?

    I saw a video of a guy doing this in an older version of Excel using the “consolidate” functions, but I didn't have any luck doing that in 2013.
    I have also tried importing the data into the master by using "data" > "from other sources" and was able to get the tables in the master as "existing connections" but I am still unable to get the information to display properly. Any assistance would be appreciated. Thanks!

  2. #2
    If you have Office 2013 Pro Plus
    try Power Query's Append. It's dynamic.
    Excel 2010 has a free Power Query Add-In.

  3. #3
    Quote Originally Posted by Herbds7 View Post
    If you have Office 2013 Pro Plus
    try Power Query's Append. It's dynamic.
    Excel 2010 has a free Power Query Add-In.
    Unfortunately, the version I am using is not compatible with Power Query. I am using Office 365. Apparently, it is only compatible with 365 ProPlus...
    thanks for the suggestion though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •