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
Spreadsheet2
Spreadsheet3
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
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!
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!