Cross-sheet formulas

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

Learn how to quickly and easily reference other sheets in your formulas. Connecting your data in Smartsheet with cross-sheet formulas, gives you the flexibility to organize your information more efficiently, make more-informed decisions, and act faster.

Follow these step-by-step instructions to setup a cross-sheet formula.

Formulas: Reference data from other sheets

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

In Smartsheet, you can use formulas to perform calculations on data stored in a single sheet. But you can also perform calculations across sheets, using those results to give you a higher level picture of what’s happening with your information. 

For example, you might use cross sheet references to 

  • Create a metric sheet for use in chart widgets.
  •  Pull data from one sheet to another without replicating the entire sheet.
  •  Display data without sharing the underlying sheet. 

Do you want to work with data in a single sheet? You might want to use sheet summary fields instead. 

Before you create cross sheet references

Ready to work with cross-sheet formulas? Keep these things in mind:

  • You must have the required permissions. See the chart below. 
  • A sheet can include no more than 100 distinct cross-sheet references. 
  • A reference range can include a maximum of 100,000 inbound cells.
  • The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula.

Required permissions

This chart shows what each user can do with cross sheet formulas in source and destination sheets:

 Capability

Owner

Admin

Editor

Viewer

View and reference data in the source sheet

Yes

Yes

Yes

Yes

Insert a formula in the destination sheet

Yes

Yes

Yes

No

Edit a reference in the formula

Yes

Yes

Yes

No

Delete sheet references used in cross-sheet formulas

Yes

Yes

Yes

No

If you have a permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you delete will also be removed from users who have access to the file you changed. When this happens, the data in cells with cross-sheet formulas will be affected.

Before you reference data 

Ready to work with cross-sheet formulas? Keep these things in mind:

  • A sheet can include no more than 100 distinct cross-sheet references. 
  • A reference range can include a maximum of 100,000 inbound cells.
  • The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. 

If you have permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you delete will also be removed from users who have access to the file you changed. The data in cells with cross-sheet formulas will be affected.