View, modify, or delete cross sheet references in formulas

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.

You can make changes to your reference directly in the formula, or, you can use the Reference Manager. Organize or clean your references: edit broken references or remove them from your formula/s, and delete unused references if you don’t plan to use them.

Edit the 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 containing the data you want to reference.

  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. Instead of Reference Another Sheet, you will now 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

The Cross Sheet References manager opens 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

Tips

Search

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

Filter

Click 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 

Create

To create a new reference, click + 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, click 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 Reference

To make a copy of a reference, click Menu > Duplicate Reference.

The name of the duplicate reference will be Copy of_original reference name.

Delete Reference

To delete a reference, click 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