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) |