Concept: Using External References to Refer to Data in Other Workbooks

In a workbook, you can refer to cells or ranges of data that exist in other workbooks. We call these references external references
or cross-workbook references
. References to other sheets in the same workbook have a similar syntax so we'll describe them in this topic also. You can make a standalone reference (by using only an equals sign and the reference) or a reference can be an argument in a formula.

A workbook that refers to (brings in) data from another workbook is a consumer
workbook. A workbook containing data that's being referred to in another workbook is a producer
workbook. A workbook can be both a consumer and a producer.

Consumer workbooks display an external reference icon on the workbook toolbar. The icon turns green if data in the producer workbook was changed after you opened the consumer workbook; click the icon to update the data. This action is the same as Recalculate All. After the producer workbook's data changes occur, there might be a delay of up to 2 minutes before the consumer workbook icon becomes green.

To add references to a producer workbook from a consumer, you must have Can Edit permission for the consumer and Can View permission or higher for the producer workbook.

When someone creates or changes a formula with an external reference, Worksheets remembers which user did it. When the formula runs, Worksheets verifies that that user has permission to access to the externally referenced workbook.

Creating External References

From a producer workbook
, you can copy a complete reference, then paste it into a consumer workbook:

Select the data that you want to reference, then right-click and select

Copy as External Reference

Open the consumer workbook, and in the cell where you want the reference, type an equals (=) sign and paste the reference by pressing Ctrl + V (Windows) or Command + V (Mac).

Alternatively, you can obtain the producer workbook's Workday ID by navigating to

File

Info

, then add the rest of the external reference manually in the consumer workbook.

From a consumer workbook
, you can copy the Workday ID of a producer workbook, then add the defined name, or sheet name and cell/range reference manually:

In the cell where you want to place the reference, select

Data

Get External Reference

. You see all the workbooks that you have access to.

Navigate to the producer workbook and then click

Copy Workday ID

In the consumer workbook, type an equals (=) sign and paste the Workday ID by pressing Ctrl + V (Windows) or Command + V (Mac). Then type the rest of the reference, which might be a defined name, or it might be a sheet name and cell/range reference.

Syntax Guidelines

For external references:

Enter the workbook's Workday ID inside square brackets, then the sheet name and cell or cell range, or the defined name. Don't include folder/path information for the producer workbook.

For cross-sheet references or external references:

Start with an equals sign (=) in the cell.

If the sheet name has either of these properties, surround the name with single quotes ('):

The sheet name doesn't start with a letter or an underscore.

Any subsequent character in the sheet name isn't a letter, a digit, a period, or an underscore.

Example cross-sheet references:

=Sheet1!A1:A4

='All Employees'!A1:A4

='All Employees'!DataArea1, where DataArea1 is a defined name

This table shows some external reference examples. Workday IDs are shortened for readability.

=[9f9a1]January!A1	Gets data from the workbook with Workday ID 9f9a1, in the sheet January, cell A1.
=SUM([9f9a1]January!A1:B6)	Shows the sum of cells in the workbook with Workday ID 9f9a1, in the sheet January, range A1:B6.
=[9f9a1]DataArea1	Gets data from the workbook with Workday ID 9f9a1, with the defined name DataArea1.
=SUM([9f9a1]Regions!A1:B6)	Shows the sum of cells in the range A1:B6, in the workbook with Workday ID 9f9a1, sheet Regions.
=SELECT("SELECT * FROM ?",ARRAYAREA([9f9a1]'Data Sheet'!A1))	Uses the SELECT function to get an array's data from the workbook 9f9a1, sheet Data Sheet.

Considerations for External References

Keep these tips in mind when working with external references:

If the data changes in a producer workbook:

Worksheets automatically updates the consumer workbook references when you open it. The update occurs regardless of the permission level of the user who opens the workbook.

When you click the green External Reference icon to update the consumer, this action is the same as Recalculate All
.

If you change the data placement in a producer, such as by deleting a sheet or inserting a column into a range, Worksheets doesn't update the reference in the consumer workbook.

Worksheets doesn't automatically update consumer workbooks that are in the trash or in manual calculation mode (

File

Settings

Manual

If you lose access to a producer workbook or a producer workbook is deleted, you see a #REF! error in workbook cells that depend on data from external references.

Worksheets reassigns broken external references in workbooks when the references become invalid due to the authoring user's account being deactivated, or when the authoring user no longer has permissions to the producer and consumer workbooks. Worksheets attempts to fix the external references whenever a user who has edit or owner to the consumer workbook, and view access or higher to the producer workbook, clicks the

External Reference

icon. If Worksheets can't update a reference, a #REF error displays and you need to update the reference manually.

If you have an external reference that contains a structured reference (live data table/column name), and you change the table name or column name, Worksheets doesn't automatically update the consumer workbook; you need to update the reference manually.

Formatting in producer workbook references is applied in the consumer workbook, unless the consumer workbook cells are already formatted. If you change the formatting in the referenced producer workbook cells later, the new formatting isn't applied in the consumer workbook. (This same behavior occurs for references in the same workbook.)

If you hover over the Workday ID in an external reference in the formula bar, Worksheets shows the name of the producer workbook.

You can't add a reference to a producer workbook chart into a consumer workbook; an error occurs when you place the reference in the consumer.

If you're the workbook owner, you can prevent users with the Can Edit permission from changing content in the producer workbook by using range protection. However, using range protection in a consumer workbook doesn't prevent updates from propagating from the producer to the consumer.

On a particular tenant, up to 10,000 consumer workbooks can refer to a single producer workbook. Up to 25 producer workbooks can provide data to a single consumer workbook.

The functions NOTIFYIF and NOTIFYIFS don't update automatically when producer workbook data changes; you need to update references manually or set up a scheduled live data refresh. (The workbook doesn't need to contain live data; the scheduled refresh performs a recalculate action, which updates the consumer workbook data.)