How to Get Excel Value From Another Workbook

Spreadsheet with Linked Value

Microsoft Excel is a business application used for working with financial data in spreadsheets and databases. The data is kept on worksheets that are stored in Excel files called workbooks. The data in separate workbooks or worksheets can be referenced in Excel calculations, even if the data is not located in the same file as the calculation. This creates a link between the two files. When the referenced data is updated, the resulting value in the calculation will be updated as well.

Things You'll Need

• Microsoft Excel

Instructions

Create Two Workbook Files to Link

1. 

   Open Excel and in the new blank workbook on the screen type a list of your expenses for various items like mortgage, gas, electric, as shown in the illustration. Add a line at the bottom of the list for the cost of magazines, but don't put a figure in there yet.

2. Save your file by clicking the "File" menu and then "Save" (or "Office button" and then "Save" in Excel, 2007) and name the file "Budget."

3. Click the "File" menu again, and then select "New Workbook" (or "Office button" and then "New" in Excel 2007) to create a new workbook file. Save this file with the title "Magazines."

4. 

   Type a list of magazines you subscribe to in the Magazines workbook and list their associated subscription costs per year.

5. 

   Enter a SUM formula at the bottom of the magazines list that totals the annual costs of the magazines by typing =SUM and then clicking and dragging over the cells containing the subscription costs. Press the Enter key to see the total value.

Link the Workbooks Through a Calculation

1. Return to your budget workbook and click into the cell where the total monthly expenses for magazines should go.

2. 

   Type = (the equals sign) then switch over to the magazines workbook by clicking the "Window menu" and the name of the Magazine file (Macintosh) or by click on the Magazines file name in your Windows tray at the bottom of the screen.

3. Click on the cell with the total cost of the magazines in it.

4. 

   Switch back to the budget workbook window and type /12 (to divide the total annual subscription costs by 12 months for the monthly budget) and then press Enter. The value from the magazines workbook will now been transferred to the budget workbook, and any time you update the magazines workbook in such a way that it changes that sum figure, the budget will be automatically updated, too.

5. 

   Close both workbooks, then reopen the Budget workbook. You'll see see a dialog box appear asking if you want to update the link with the Magazine file. Click the Update button to keep the value you got from the Magazine workbook up to date.

Tips & Warnings

• Resize the workbook windows you want to link values between to be side-by-side for easier cell selection.

• If you click "Ignore" and don't update links between workbooks when you open them, your calculations may be wrong.

Resources

• Create Links to Cells in Other Workbooks
• Manage Links to Other Workbooks
• Microsoft Excel Home Page