Skip to main content
Documentation
Documentation
Workday User Guide
Last Updated: 2025-07-25
Concept: Using External References to Refer to Data in Other Workbooks

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 to Producer Relationship

Creating External References

To add references to a producer workbook from a consumer workbook, you must have Can Edit permission for the consumer workbook 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 the user has permission to access the externally referenced workbook.
From a producer workbook
, you can copy a complete reference and then paste it into a consumer workbook.
  1. Select the data that you want to reference and then right-click and select
    Copy as External Reference
    .
  2. Open the consumer workbook. 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
 and adding 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 and then add the defined name or sheet name and cell/range reference manually.
  1. In the cell where you want to place the reference, select
    Data
    Get External Reference
     to see all the workbooks that you have access to.
  2. Navigate to the producer workbook and then click
    Copy Workday ID
    .
  3. 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 a sheet name and cell/range reference.

Updating External References

Consumer workbooks display a gray
External References
 icon on the workbook toolbar. If data in the producer workbook changes, the icon turns green and you can click it to update the data in the consumer workbook. The
External References
 icon is accessible to users with any level of workbook permission. The update that occurs is the same as
Recalculate All
. There might be a delay of up to 2 minutes, once the data in the producer workbook changes, before the consumer workbook
External References
 icon turns green.
When producer workbook data changes, you can choose to have the external references update automatically when the consumer workbook is opened, or you can open the consumer workbook and click the
External References
 icon to update the external references manually. Click
File
>
Settings
and either select or deselect the
Updates data from external workbooks each time the workbook is opened
 check box.
Additionally, you can schedule a live data refresh to prompt the consumer workbook to be recalculated. You can set up a scheduled refresh to automate a recalculation even if the consumer workbook doesn't contain live data.
For example, if you have 3 workbooks joined together (Producer > Consumer/Producer > Consumer) you can automate updates to each workbook by scheduling data refreshes. Additionally, staggering the scheduled refreshes ensures that the data in one workbook is up to date before the data in the next workbook is recalculated.
This approach automates the data flow between the workbooks, eliminating the need for manual updates.
  1. Schedule a refresh for the producer/consumer workbook.
    In the producer/consumer workbook, click
    Data
    >
    Schedule Live Data Refresh
     and set a refresh time such as 12:00 PM. This will update the data pulled in from the producer workbook.
  2. Schedule a refresh for the consumer workbook.
    In the consumer workbook, schedule a refresh time after the producer/consumer refresh, such as 12:15 PM. This ensures the consumer workbook always displays the latest recalculated data.
The
Schedule Live Data Refresh
 option is only configurable by the owner of the workbook and scheduled refreshes can only run once per day.

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:
    • When you click the green
      External References
       icon to update the consumer workbook, the 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.
  • 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.)

This content is not part of the Workday Administrator Guide.