Create cross-sheet references

Use cross-sheet references to:

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

Use sheet summary fields to work with data in a single sheet instead.

Before you create cross-sheet references

• You must have the required permissions.
• 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. 

Required permissions

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

Be careful when deleting sheet references. Any sheet reference you delete will also be removed from users who can access the file you changed, impacting the data in cells with cross-sheet formulas.

Create a cross-sheet reference 

1. Build the formula in the cell where you want the data to appear.
   For example, type =COUNT(
   The formula help card appears above or below your cell. 
2. In the help card, select Reference Another Sheet.

Brandfolder Image

3. Search for the sheet where the data exists.
4. In the search results, select the source sheet.
5. Select the cell range containing the data you want to reference and select Insert Reference.

Select a column header to reference the whole column, even if rows get added or deleted from the source. To reference multiple columns, hold the Shift key, then select the first and last column - you’ll get everything in between. You can only choose contiguous columns. To select individual columns, create multiple references. 

3. Optional: In the Sheet reference name text box, type a name for your reference. If you don’t enter a name, the reference has a default name based on the sheet name and the number of times that sheet is referenced. The name doesn’t impact the sheet; it’s so you can identify the reference in the formula. 

That’s it! You should now see your reference added to your formula. Here’s an example of how your formula should appear:

=COUNT({my_sheet1 Range1})

Tips for working with references

• Don’t include any data you don’t need to see in the destination sheet. All information in the reference range is available and may appear in the destination sheet. Try using an INDEX(MATCH formula instead of VLOOKUP, which might reference unnecessary data. 
• Ensure new rows are in the reference range. Rows added below a referenced range won’t be included. To include all rows, select the columns as the reference.
• Re-use references. You don’t have to open the Reference Another Sheet dialog to use an existing reference. In the new formula, add braces { }, then type or paste the reference name in them. 
• References are unique to the sheet on which they were created. You must create new references for each sheet.
• Cells containing cross-sheet formulas don't trigger actions that change the sheet automatically. This includes formulas that refer to another cell with a cross-sheet formula.

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