View, modify, or delete cross sheet references in formulas

You can make changes to your reference directly in the formula, or, you can use the reference manager.

Who can use this?

Plans:

  • Pro
  • Business
  • Enterprise

Permissions:

  • Owner
  • Admin
  • Editor

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

Edit the formula

You can edit a reference directly in your formula without opening the Sheet Reference Manager.

  1. Select the cell that contains your formula. A summary of the formula appears in the cell.
  2. Click the name of the reference that you’d like to edit.
  3. You will see a link to Edit Reference. This will open the reference another sheet dialog where you can change the reference name or choose a new sheet or cell range.

 

When you edit a reference, all its instances in the sheet will update in real-time. If want to keep the structure from a formula in your sheet but want to change the range, it’s better to delete the text from the current reference and create a new reference.

 

Edit in the reference manager

In 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 box

The cross sheet references manager opens by default, but you can switch to the cell link references manager using the left panel. Learn more about cell links.

Use the following table for details on each of the features in the cross sheet references manager:

Feature
 		ActionTips
SearchIn the search list… box, type keywords to find references you need to use.Use a conventional naming structure for your references, such as sheet name_columns referenced.
FilterOpen the All References drop-down list to set the filter to Active, Processing, Broken, In Use, or Unused.

Filters help you find, categorize, or review references.

  • Active: Up-to-date references
  • Processing:  References loading updates from the source data
  • Broken: Deleted source rows, columns, or cells
  • In Use: References you’re using in cross-sheet formulas
  • Unused: References that are not in any formulas in your sheet 
CreateTo create a new reference, select + Create.You can use this reference when you create new formulas.
Edit Reference

Steps in editing references:

  1. To display the Menu icon , hover over any reference in the list. 
  2. Then, select Edit Reference to change the name, referenced cell range, or source sheet. 
If the reference you are editing is in use, all its instances in your formulas will be updated.
Duplicate ReferenceTo make a copy of a reference, select Menu > Duplicate Reference.The name of the duplicate reference will be Copy of_original reference name.
Delete ReferenceTo delete a reference, select Menu > Delete Reference.If the deleted reference is in use, the formulas may return #INVALID REF errors.

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