Formulas: Reference data from other sheets

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 ametric sheetfor 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 usesheet summary fieldsinstead.

在你创建跨表引用

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:

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.

Still need help?

Use theFormula Handbook templateto 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