Follow these step-by-step instructions to setup a cross-sheet formula.
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.
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.
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|
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:
- 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.)
- In the help card, click Reference another sheet.
- 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.
NOTE: Currently only a contiguous range is allowed.
- 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:
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.
- Select the cell that contains your formula. A summary of the formula appears on the left or right of the cell.
- 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.
Delete a Cross-Sheet Reference
To completely delete a reference:
- Navigate to the source sheet.
- Select one of the cells in the reference (they'll contain a gray arrow icon to the right of their value).
An inline tooltip appears with the name of the destination sheet.
- Hover over the inline tooltip and select Delete.
IMPORTANT: Deleting a reference removes it from all formulas in the destination sheet. When deleting a reference, make sure that you're not using it in other critical formulas on the destination sheet.
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—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.