Multiple Worksheet Consolidation

rmcelroy

New member
Joined
Oct 17, 2014
Messages
2
Reaction score
0
Points
0
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
dataA1dataA2dataA3
dataB1dataB2dataB3
dataC1dataC2dataC3

Spreadsheet2
Col1
Col2
Col3
dataD1dataD2dataD3
dataE1dataE2dataE3
dataF1dataF2dataF3

Spreadsheet3
Col1
Col2
Col3
dataG1dataG2dataG3
dataH1dataH2dataH3
dataI1dataI2dataI3


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
dataA1dataA2dataA3
dataB1dataB2dataB3
dataC1dataC2dataC3
dataD1dataD2dataD3
dataE1dataE2dataE3
dataF1dataF2dataF3
dataG1dataG2dataG3
dataH1dataH2dataH3
dataI1dataI2dataI3


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!
 
If you have Office 2013 Pro Plus
try Power Query's Append. It's dynamic.
Excel 2010 has a free Power Query Add-In.
 
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.
 
Back
Top