Tutorial Excel : Using External Cell References

You may find that you have data in one workbook that you want to reference in a formula within another workbook. In such a situation, you can create a link between the workbooks using an external cell reference. An external cell reference is nothing more than a cell reference that resides in an outside workbook. The benefit of using an external cell reference is that when the data in the outside workbook changes, Excel automatically updates the value returned by the external cell reference.

Creating an external cell reference is relatively easy. Open both workbooks (the workbook that you’re currently working in and the outside workbook). While entering a formula in the workbook you’re currently working in, click the cell that you want to reference in the outside workbook.

As you can see in Image 1-3, you’ll immediately be able to tell that the cell reference is an external reference due to the full file path and sheet name prefixing the cell reference.

Image 1-3: An example of an external cell reference.

All external cell references have the same component parts, as follows:
      'File Path[Workbook Name]Sheet Name'!Cell Reference
Here’s a breakdown of these parts:

• File Path: This part of the cell reference points to the drive and directory in which the workbook is located.
• Workbook Name: This part of the cell reference points to the name of the workbook. This part is always enclosed in brackets ([ ]) and always includes the file extension (.xlsx, .xls, .xslm, and so on).
• Sheet Name: This part of the cell reference points to the name of the sheet in which the referenced cell resides.
• Cell Reference: This part of the cell reference points to the actual cell that is being referenced.

Categories: Formula