Create cross sheet references to work with data in another sheet

Applies to

Smartsheet
  • Pro
  • Business
  • Enterprise

Capabilities

Who can use this capability

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

Forumla help card open over a cell with =COUNT( showing as an example.

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 will appear above or below your cell. 
  2. In the help card, click Reference Another Sheet.
  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 click 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 select contiguous columns. To select individual columns, create multiple references. 
  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 have 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 where they were created. You have to make new references for each sheet.
  • 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.