Cross-sheet formulas

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

  • Owner
  • Admin
  • Editor

 

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?

  • Owner
  • Admin
  • Editor

 

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. 

Users in an Enterprise plan can use AI to generate formulas. Learn more.

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:

 CapabilityOwnerAdminEditorViewer
View and reference data in the source sheetYesYesYesYes
Insert a formula in the destination sheetYesYesYesNo
Edit a reference in the formulaYesYesYesNo
Delete sheet references used in cross-sheet formulasYesYesYesNo

If you have permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you remove 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.

Still need help?

Use the Formula Handbook template to find more support resources and view 100+ formulas, including a glossary of every function that you can practice working with in real time and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community