Cross-sheet formulas

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

Perform calculations on data that is stored in another sheet with cross-sheet formulas. 

For example, use the VLOOKUP function to find a value from a lookup table on a different sheet you have access to. Or, use the SUMIF formula to get the sum of values you have stored on a separate sheet.

To create cross-sheet formulas, see create and edit formulas in Smartsheet.

Required Permissions

Before you learn how to work with cross-sheet formulas, review what each user can do 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. 

Reference Data from Another Sheet

You can only reference data from a single sheet. To pull data from separate sheets, create multiple references:

  1. In the desired cell, build your formula.
    For example, type =VLOOKUP(. After you type the function name and the left parenthesis, the formula help card will appear above or below your cell. 
  2. In the help card, click Reference another sheet.

    help-card
     
  3. Search for the sheet where the data exists.
  4. In the search results, select the source sheet.

    Reference Another Sheet
  5. Select the cell range containing the data you want to reference, and then click Insert Reference.

    NOTE: Currently only a contiguous range is allowed.
    Insert Reference
    TIP: 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’d like to reference - all columns in between will be selected.
  6. Optional: In the Sheet reference name text box, type a name for your reference. If you don’t enter a name, the reference will be assigned a default name based on the sheet name and the number of times that sheet has been referenced.

That’s it! You should now see your reference added in your formula. Here’s an example of how your formula should appear:
 
=COUNT({my_sheet1 Range1})

To prevent infinite approval loops, cells that contain cross-sheet formulas or cell links will not trigger automation that changes the sheet (e.g., Move row, Copy row, Lock row, etc.). To work around this, consider using time-based or recurring automated workflows.

Manage Sheet References

View, Modify, and Delete References with the Sheet Reference Manager

In the Sheet Reference Manager, you can see and modify all Cross Sheet References and Cell Link References in your sheet. To open it, right-click any cell in your sheet and select Manage References…

Sheet Reference Manager

You’ll be taken to the Cross Sheet References manager by default, but you can switch to the Cell Link References manager using the left panel. See Cell Links: Consolidate or Maintain Consistency of Data for more information about cell links.

Use the following table for details on each of the features in the Cross Sheet References manager:

 

Use this feature

To do this 

Tips

Search

In the Search list… box, type keywords to find references you need to use.


 

Use a conventional naming structure for your references such as sheet name_columns referenced.

Filter

Click the All References drop-down list to set the filter to Active, Processing, Broken, In Use, or Unused.

Filters help you easily find, categorize, or review references. 

  • Active: Up-to-date references
  • Processing:  References loading updates from the source data
  • Broken: Deleted source rows, columns, or cells
  • In Use: References you’re using in cross-sheet formulas
  • Unused: References that are not in any formulas in your sheet 

Create

To create a new reference, click + Create

You can use this reference when you create new formulas.

Edit Reference

Steps in editing references:

  1. To display the Menu icon , hover over any reference in the list. 
  2. Then, click Edit Reference to change the name, referenced cell range, or source sheet. 

If the reference you are editing is in use, all its instances in your formulas will be updated in real time.

Duplicate Reference

To make a copy of a reference, click Menu > Duplicate Reference

The name of the duplicate reference will be Copy of_original reference name.

Delete Reference

To delete a reference, click Menu Delete Reference

If the deleted reference is in use, the formulas may return #INVALID REF errors.

 

Edit a Reference Directly in your Formula

You can edit a reference directly in your formula without opening the Sheet Reference Manager. 

You must have Editor-level sharing or greater permissions on a sheet to edit the reference. You must also have Viewer-level sharing permissions or greater on the sheet that contains the data you want to reference.

  1. Select the cell that contains your formula. A summary of the formula appears in the left or right of the cell.

    reference-summary
     
  2. Click the name of the reference that you’d like to edit. 

Once you’re in the Reference Another Sheet dialog, you can change the reference name, or choose a new sheet or cell range. 
 
When you edit a reference, all its instances in the sheet will update in real time.

Tips for working with references

  • Set your reference data. Once you choose a sheet and see the range picker, you can select the individual cell, cell range, or complete columns you’d like to reference.
  • 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. 
  • Ensure new rows are in the reference range. Rows added below a referenced range will not be included. To include all rows, select the columns as the reference.
  • Use multiple references to other sheets in the same formula, if needed.
  • Edit the formula to use a reference you created. 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 where they were created. You have to make new references for each sheet.. 
  • Open the Reference Another Sheet dialog through the in-line help card. Hover over the reference name while editing a formula. Then, click the link in the Edit Reference section.
  • Organize or clean your references. Edit Broken references, or remove them from your formula/s. Delete Unused references too, if you don’t plan to use them.
  • Add an Unused reference in your formula, if necessary. Type a formula in a cell. Then, add braces and put the Unused reference’s name in them.