Create an external reference (link) to a cell range in another workbook
You can refer to the contents of cells in another workbook by creating an external reference. An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook. You can refer to the specific cell range, a defined name for the cell range, or define a name for the external reference.
What do you want to do?
Learn more about external references
Although external references are similar to cell references, there are important differences. You use external references when working with large amounts of data or complex formulas that are spread across several workbooks. You create them in a different way and they appear differently in a cell or in the formula bar.
Where external references can be used effectively
External references are especially useful when it is not practical to keep large worksheet models together in the same workbook.
-
Merge data from several workbooks You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. That way, when the source workbooks are changed, you won't have to manually change the summary workbook.
-
Create different views of your data You can enter all of your data into one or more source workbooks, and then create a report workbook that contains external references to only the pertinent data.
-
Streamline large, complex models By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.
Ways to create external references
If you use a cell reference to create an external reference, you can then also apply formulas to the information. By switching between the various types of cell reference, you can also control which cells are linked to if you move the external reference. For example, if you use a relative reference, when you move the external reference, the cell or cells that it links to change to reflect its new position on the worksheet.
When you create an external reference from one workbook to another workbook, you should use a name to refer to the cells that you are linking to. You can create an external reference by using a name that is already defined, or you can define the name when you create the external reference. By using a name, it is easier to remember the contents of the cells that you are linking to. External references that use defined names do not change when you move them because a name refers to a specific cell or range of cells. If you want an external reference that uses a defined name to change when you move it, you can change the name that is used in the external reference, or you can change the cells that the name refers to.
What an external reference to another workbook looks like
Formulas with external references to other workbooks are displayed in two ways, depending on whether the source workbook — the one workbook that supplies data to a formula — is open or closed.
When the source is open, the external reference includes the workbook name in square brackets ([ ]), followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.
| External reference |
| =SUM([Budget.xlsx]Annual!C10:C25) |
When the source is not open, the external reference includes the entire path.
| External reference |
| =SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25) |
Note: If the name of the other worksheet or workbook contains nonalphabetical characters, you must enclose the name (or the path) within single quotation marks.
Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!) and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xlsx.
| External reference |
| =SUM(Budget.xlsx!Sales) |
Create an external reference between cells in different workbooks
-
Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
-
In the source workbook, click Save
on the Quick Access Toolbar. -
Select the cell or cells in which you want to create the external reference.
-
Type = (equal sign). If you want to perform calculations or functions on the external reference value, type the operator or function that you want to precede the external reference.
-
Switch to the source workbook, and then click the worksheet that contains the cells that you want to link to.
-
Select the cell or cells that you want to link to.
-
Return to the destination workbook, and notice that Excel adds the reference to the source workbook and the cells that you selected in the previous step.
-
Optionally, in the destination worksheet, edit or change the formula.
-
Press CTRL+SHIFT+ENTER.
Create an external reference to a defined name in another workbook
-
Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
-
In the source workbook, click Save
on the Quick Access Toolbar. -
Select the cell or cells in which you want to create the external reference.
-
Type = (equal sign). If you want to perform calculations or functions on the external reference's value, type the operator or function that you want to precede the external reference.
-
On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.
-
Press F3, and then select the name that you want to link to.
Define a name that contains an external reference to cells in another workbook
-
Open the destination workbook and the source workbook.
-
In the destination workbook, on the Formulas tab, in the Defined Names group, click Define Name.
-
In the New Name dialog box, in the Name box, type a name for the range.
-
In the Refers to box, delete the contents, and then keep the cursor in the box.
If the name contains a formula, enter the formula, and then position the cursor where you want the external reference. For example, type =SUM(), and then position the cursor between the parentheses.
-
On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.
-
Select the cell or range of cells that you want to link to.
-
In the New Name dialog box, click OK.
No comments:
Post a Comment