Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say I have a directory with the following files:

  • Workbook1.xlsx (Contains Sheet1, Sheet2 and Sheet3)
  • Workbook2.xlsx (Contains Sheet4 and Sheet5)
  • Workbook3.xlsx (Contains Sheet 6)

How can I create a master workbook that contains all spreadsheets?

i.e., MasterWorkbook.xlsx (Contains Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and Sheet6)

Thanks,

Ignacio

share|improve this question

migrated from stackoverflow.com Feb 23 '11 at 0:21

This question came from our site for professional and enthusiast programmers.

    
I have voted to move this question, however, @iggymoran, if you would like to add some notes on coding, it probably will not get the rest of the close votes. –  Remou Feb 22 '11 at 16:29

3 Answers 3

Ignacio,

There is more than one way to do it, you'll need to pick the one based on what you're trying to achieve.

Method 1 (using data source connection): A little bit more complex, but worth if you have a well defined structure in your files. Is required to name ranges in each sheet. You can obviously name a whole sheet as a range, but as smaller the range, the better performance you'll have.

  • Open Workbooks1.sheet1
  • Select the whole source data you want
  • Name this range (by clicking on the upper-left text space that contains the data address, i.e. A1

    Step 1 is done, you have a named range with the source data you want

  • Now, go for the MasterWorkbook

  • Data Ribbon (I assume you're using Excel 2007, due to the xlsx files)
  • Connections
  • Add
  • Seach more...
  • Connect to a new data source
  • DSN ODBC
  • Excel Files
  • select your Workbook1 file
  • Select the range you defined previously
  • Finish
  • Close

Step 2 is done; you have a data source connection to the data at spreadsheet1

  • Click in Existing connections
  • Add the source you created

Done, you have a link to the data at worksheet1.sheet1

Method 2 (using cell level relationship): More straightforward, however I believe that's slower and more error prone.

  • Open Workbooks1
  • Open MasterWorkbook
  • On Masterworkbook.Sheet1.A1, type "=" [enter]
  • Click on Workbooks1.Sheet1.A1 [enter]
  • Take a look on the address generated; it's a reference to the Workbooks1.xlsx
  • Remove the $ from $A$1 and drag the formula. Initially for the columns, and then for the rows
  • Voilà, you have a relationship between the Workbooks

Notice that you'll only be able to see the data in MasterWorkbook; any change in Workbook cells with pointers will raplace that formula.

Edit: There are other solutions as well, as you can see HERE

share|improve this answer

Ron de Bruin has created a fabulous Windows plugin for merging Excel worksheets, called RDBMerge. Instructions can be found here: http://www.rondebruin.nl/merge.htm. It worked flawlessly for me, merging xlsx files in Excel 2007.

share|improve this answer

Do you want an automatic way to do it (i.e. a macro or similar), or do you only need to do it once?

If you only need to do it once, then you can just move all of the worksheets to the same workbook (file).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.