• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 3. Building Basic Formulas > Understanding External References

Understanding External References

There's no big mystery behind these links. You set up links by including an external reference to a cell or range in another workbook (or in another worksheet from the same workbook). In the example shown in Figure 3.11, all I did was enter an equals sign in cell B2 of the Budget Summary worksheet, and then click cell R7 in the Details worksheet.

The only thing you need to be comfortable with is the structure of an external reference. Here's the syntax:


pathThe drive and directory in which the workbook is located. You need to include the path only when the workbook is closed.
workbooknameThe name of the workbook, including an extension. Always enclose the workbook name in square brackets ([ ]). You can omit workbookname if you're referencing a cell or range in another sheet of the same workbook.
sheetnameThe name of the worksheet's tab. You can omit sheetname if reference is a defined name in the same workbook.
referenceA cell or range reference, or a defined name.

For example, if you close the 2005 Budget workbook, Excel automatically changes the external reference shown in Figure 3.11 to this (depending on the actual path of the file):

='C:\My Documents\Worksheets\[2005 Budget.xls]Details'!$R$7


You need the single quotation marks around the path, workbook name, and sheet name only if the workbook is closed or if the path, workbook, or sheet name contains spaces. If in doubt, include the single quotation marks anyway; Excel happily ignores them if they're not required.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint