How to Use a Reference in a Different Worksheet in Excel

Excel is very handy in it’s ability to link multiple spreadsheets together. Pulling data from multiple sources eliminates duplicate entry of data and increases work efficiency. A fellow Ehow member requested this article, so here’s my shot at it.

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• MS Excel

1. First open the spreadsheets you want to link up. Minimize the files and resize so you can see both or all the open spreadsheets. Do this by clicking on the line icon in the top right corner of your screen. Then click on Window from the menu bar across the top of the window and choose Arrange. When the Arrange Window appears, choose Tiled and click OK.
2. Activate or click on the new cell you want the linked data from a different spreadsheet to appear. While in that cell, press the = key. Activate the worksheet you want to pull the data from by clicking anywhere in the spreadsheet with your mouse. Then use your mouse to click again in the specific cell you want to link to. You’ll notice that the cell reference automatically populates the new cell where you want the data to appear. Also, the cell that is being linked to is highlighted with a chasing dotted line. Press enter to complete the link.
3. Let’s look at the formula for a second. In the example, it looks like this: ='[Fruit prices.xls]Fruit'!$B$2 The file name is inside the brackets, the worksheet tab name comes next, and finally you see the cell reference. The cell reference is anchored by dollar signs before the specified column and row. This means that if the cell contents of the originating file are ever moved by sorting or formatting, Excel will make sure your link is updated with the new position.
4. If you close the files from which the data is linked to, you’ll notice the formula has changed slightly to include the hard drive and folder where the file has saved. Excel populates this information automatically. ='C:\Melody - Personal\[Fruit prices.xls]Fruit'!$B$2
5. The next time you reopen this file, you’ll see a message notifying you that the file contains automatic links to information in another workbook, and asking if you want to update the workbook with changes made to the other workbook. Say yes to pull the most current, updated data.
6. Occasionally, you may reorganize your files to different drives or folders. This can screw up linked data and Excel doesn’t know where to pull it from. When this happens, File Not Found window will appear. You can browse this window to find where the missing workbooks are now located.
7. You can also update links by clicking on Edit from the menu bar across the top of the screen. Then choose Links.
8. A Links Window will appear listing all the source files linked to in the open workbook. Here, highlight a source file and choose to update, open the source, or change the source.
9. When you click on the Change Source button, a window will appear that will allow you to browse for a new/different file or the file which may be in a new location. Click OK when complete. Then click the OK button in the Links Window.