Follow these step-by-step instructions to setup a cross-sheet formula.
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.
Video MP4
Formulas: reference data from other sheets
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:
Capability |
Owner |
Admin |
编辑器 |
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.
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.