Referencing cells in an Excel spreadsheet and using the data quickly for calculations automatically is one of the spreadsheet programs greatest abilities. What if your information is held in another worksheet or even another workbook? Can you reference the cells in different sheets or even different files? Yes, indeed you can. In this article, we’ll start with the basics and you’ll find the formulas needed to do just that, screenshots demonstrating it, and two excel files to download to help see the formula in action.
General
A normal reference (utilizing cells within the same sheet), as an example, for cell D3 to be the product of the contents of cell D1 and the contents of cell D2 would use this formula:
=D1*D2
By default, a new workbook starts with 3 worksheets respectively named Sheet1, Sheet2, and Sheet3. When you start using multiple sheets, you should rename the sheets something unique and descriptive and delete any unused sheets. Right-click on the tab of a sheet in the bottom-left corner in order to Rename a sheet to something describing the content or the function of the worksheet. You can also choose to delete a sheet from the right-click menu if you need to remove the sheet from the workbook.
If you need to rename the sheet later, references using the name will automatically be updated, so no need to worry if you change your mind about a name.
Linking to Another Sheet in the Same Workbook
You can follow along with this example by downloading the linking_sheets.xls workbook.
Linking_sheets.xls is a workbook that contains three worksheets inside of it: Main, Triple, and TripledCost. Main contains the Quantity, Cost, and total. Quantity and cost are manually entered while total is calculated by multiplying the quantity by the cost for each row.
The Triple sheet pulls the Quantity from the main sheet for column A. This way if the quantity were to change, you would only have to change it in the main sheet and all your formulas in the other sheets that are derived from that quantity are updated automatically. Column B in this sheet just takes the value in column A of the same row and multiplies it by 3. As you can see in the screenshot, the formula to pull from a cell in another sheet is:
=SheetName!Cell
Or, in this example:
=Main!A6
The third sheet, TripledCost, pulls the tripled quantity (column B) from the Triple sheet for column A. It then populates column B with the cost from the Main sheet’s column B. Column C is calculated by multiplying column A by column C.
Row 6, Column A:
=Triple!B6
Row 6, Column B:
=Main!B6
Row 6, Column C:
=A6*B6
If the quantity or cost were to change, you would only have to update them in the Main sheet and all of the other calculations in the workbook that derive their values from that information would be automatically updated.
Linking to a Sheet in a Different Workbook
You can follow along with this example by downloading the linking_wkbks.xls workbook and using it along with the linking_sheets.xls file used above.
Open both Excel files that you want to use. One will be the source and the other will be the destination. The file with the data you want will be the source and the file where you want the data will be the destination. When you have both files open, switch to the View tab and click the button “View Side by Side”. This should automatically arrange the two workbooks next to each other neatly.
Now, go to the cell where you want the data and select it. In the formula for that cell enter ‘=’.
Now you can enter the formula following this formula, assuming the files are in the same directory. If not, you can specify the path between the equals sign (=) and the left square bracket ([).
=[filename.xls]SheetName!$Column$Row
Or in this example:
=[Linking_sheets.xls]Triple!$B$4
An easier and more accurate way is just to click the cell you want in the source worksheet after you entered the equals sign in the previous step. You should see a formula in the same format as the one above fill in the formula bar as a dashed border highlights the source cell. Click Ok and you’ll be all set.
The Fill Handle, in my experience, doesn’t work in the same way for external sheets with automatically incrementing the formula. If somebody knows a fix to this, please explain in the comments. You’ll also want to be careful about renaming files as this will not automatically update workbooks referencing them.
The linking_wkbks.xls file has one sheet. The first column is pulled from the Total Cost column of the TripledCost sheet of the linking_sheets.xls file. Column B is just manually entered numbers and Column C is calculated from Column B minus (-) Column A.
Source: http://office.microsoft.com/en-us/excel/HP051995141033.aspx