Create cross sheet references

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

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 appears above or below your cell. 
2. In the help card, select 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 select 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 has 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.
• Cells containing cross-sheet formulas don't trigger actions that change the sheet automatically. This includes formulas that refer to another cell with a cross-sheet formula.

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community