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)
- Seach more...
- Connect to a new data source
- DSN ODBC
- Excel Files
- select your Workbook1 file
- Select the range you defined previously
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