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 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.

Required Permissions

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
Owner Yes Yes
Admin Yes Yes
Editor Yes Yes
Viewer Yes No

Before You Begin

Here are some things 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 100,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
  • 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. For more information on this and other formula errors, see Formula Error Messages.

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.
    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.
  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}) 

 

Manage Cross Sheet References

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

With 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

Search

In the upper-left corner, type into the search bar to find references by name.

TIP: As a best practice, use a conventional naming structure for your references such as {<sheet name> - <columns referenced>}

Filter

Select the filter icon next to the search bar to filter to Active, Processing, Broken, In Use, or Unused references.

Active references are up to date, and processing references are loading updates from the source data. 

References may be broken if the source rows, columns, or cells have been deleted. You can edit it to reference valid data or delete the reference altogether.

References that are not currently in any formulas on your sheet will appear with the Unused filter, and you can delete these references if you don’t plan to use them. To use an unused reference, type a formula in a cell and enter the reference name surrounded by curly braces.

Create

Select +Create from the top-right corner to create a new reference. You can use this reference later when you create new formulas.

Menu > Edit Reference

Hover over any reference in the list to display the menu icon to the right, then choose Edit Reference to change the  name, referenced cell range, or source sheet. If this reference is currently in use, all instances of this reference in your formulas will be updated.

Menu > Duplicate Reference

Choose Duplicate Reference from the menu to make a copy. The name of the copied reference will be “Copy of <original reference name>:

Menu > Delete Reference

Choose Delete Reference to delete the reference from the sheet. If the reference is currently 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 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:

  • 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—surrounded by {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 Reference Another Sheet form.

edit-reference-button