Formulas: Reference Data from Other Sheets

Perform calculations on data that is stored in another sheet. For example, use the VLOOKUP function to find a value from a master lookup table that exists on a different sheet that you have access to, or create a SUMIF formula that summarizes values you have stored on a different sheet.

To create formulas that reference data from other sheets, we recommend that you first have knowledge of how to create formulas in Smartsheet. For more information on creating formulas, take a look at the help center article Create and Edit Formulas in Smartsheet.

Before You Begin: Requirements and Limitations

Here’s a breakdown of the sharing permissions people can have on the source and destination sheets when working with formulas across multiple sheets. For more information on sheet sharing permissions, see Sharing Permission Levels.

 

Sheet with the data that you want to reference

Sheet where you want to insert a formula
Ownerxx
Adminxx
Editorxx
Viewerx 

Functions that Don't Support References from Another Sheet

The following functions currently don’t support references from another sheet:

  • CHILDREN
  • PARENT
  • ANCESTORS

Using a reference from another sheet will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. For more information on this and other formula errors, see Formula Error Messages.

Limitations and Important Notes

Here are some limitations to keep in mind as you work with cross-sheet formulas:

  • All the data in the range being referenced is available and potentially visible in the destination sheet, meaning you shouldn’t include any data in the reference that you don’t want available in the destination sheet.
  • Each sheet can include no more than 100 distinct cross-sheet references. 
  • There’s a limit of 25,000 inbound cells that can be referenced from other sheets into one sheet in total.
  • When you create a reference to another sheet, you can only reference data from a single sheet. If you need to reference data from multiple sheets, you’ll need to create multiple references using the steps below.

Reference Data from Another Sheet

To reference data from another sheet in your formula:

  1. Start building your formula by typing the equal sign (=), function name, and the left parenthesis in the desired cell. For example, type =VLOOKUP(

    After you type the function name and left parenthesis, the formula help card appears above or below your cell. (See Create and Edit Formulas in Smartsheet for more information about building formulas.)
     
  2. In the help card, click Reference another sheet.

    help-card
     
  3. In the Reference Another Sheet form, select the sheet where the data exists, and then select the range of cells containing the data you want to reference.

    name-reference

    NOTE: Currently only a contiguous range is allowed.
     
  4. You can optionally 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! Your reference will appear in your formulas surrounded by curly braces. For example: 
=COUNT({my_sheet1 Range1}) 

Edit a Reference

You can edit the name, referenced cell range, and source sheet of the reference.

NOTE: 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 on the left or right of the cell.

    reference-summary
     
  2. Click the name of the reference that you’d like to edit. The Reference Another Sheet form appears.

Once in the Reference Another Sheet form, you can change the reference name, or choose a new sheet or range of cells. When you edit a reference, all instances of that reference on the sheet are updated automatically to reflect the change.

Tips for Working with References

Here are a few tips that can help as you create and use references to other sheets in your formulas:

  • While building your formula, you can click on another sheet tab (at the top of the Smartsheet window) to quickly reference data from that sheet.
  • Once the range picker is open and the sheet is selected, you can select the individual cell, range of cells, or complete columns that you’d like to reference.
  • Rows added below a referenced range will not be included in the reference. To include all rows, select the columns as the reference.
  • You can use multiple references to other sheets in the same formula.
  • To use an already created reference, you can type or copy the name of the reference—including the curly braces—into a new formula.
  • References are unique to the sheet they’re created on and can’t be used on other sheets without recreating them.
  • While editing a formula you can put your cursor within the name of the reference to show an “Edit Reference” link in the in-line help card. You can click this link to open the Sheet Picker form.

    edit-reference-button

Video Demos

 

Was this article helpful?
YesNo