Why Smartsheet Formulas Show #REF

Updated 2026-10-15

A formula that read another sheet happily for months starts returning an error, and nothing on the sheet looks different. The cause is usually not the formula — it is the cross-sheet reference the formula reads through, the named range in curly braces like {Budget Range 1}. Smartsheet keeps the reference and the formula apart, so the reference can go bad while the formula stays exactly as you wrote it. Here is how to read a reference’s status on the sheet in front of you, what each error message actually means, and how to find the rest of them.

Read a reference’s status on one sheet

Smartsheet calls this the sheet reference manager, and it is reached from a cell rather than from a menu at the top of the sheet — which is why plenty of people who use cross-sheet formulas every day have never opened it. Managing references needs Owner, Admin or Editor permission on the sheet, and is available on the Pro, Business and Enterprise plans.

  1. Right-click any cell on the sheet that holds the formula and select Manage References. The cross-sheet references manager opens by default; the left panel switches to the cell link references manager if you need that instead.
  2. Open the All References drop-down list to filter what you are looking at. The filters are Active, Processing, Broken, In Use and Unused. Broken is the one to read first: it means the source rows, columns or cells the reference points at were deleted.
  3. You can also search the list by keyword — useful when the name in your formula is one of many near-identical ranges.
  4. To repoint one, hover over it, open its Menu and choose Edit Reference, which lets you change its name, its cell range or its source sheet. If the reference is in use, every instance of it in your formulas is updated with it. You can do the same from inside a formula by selecting the reference name there and choosing Edit Reference.
  5. Be careful with Delete Reference. Deleting removes the reference for everyone who can access the sheet, and formulas still using it may return #INVALID REF.

The error message tells you which kind of break it is

“#REF everywhere” is how people describe this, but Smartsheet documents several distinct formula errors here, and the one you are looking at narrows the cause considerably:

#REF — the formula points at a cell that does not exist, which can happen after a row or column is deleted. #INVALID REF — the name inside the curly braces does not match any existing cross-sheet reference, so either it was mistyped or the reference itself was deleted. #CIRCULAR REFERENCE — the formula references itself, directly in its own text or indirectly through other cells that lead back to it. #BLOCKED — the calculation cannot run because at least one cell the formula references contains an error, so the thing to fix is upstream. #PERMISSION ERROR — the formula references another sheet, but nobody has the required access to both sheets.

That last one is the one that surprises people, because it has nothing to do with your formula or your data: a cross-sheet reference needs a user in common across both sheets, so somebody leaving or being unshared from one sheet can stop a formula on a sheet they never touched.

Why the next one is so hard to find

The reference manager is per sheet, and references themselves are per sheet — Smartsheet’s documentation is explicit that references are unique to the sheet on which they were created, so the same source range referenced from four sheets is four separate references with four separate statuses. A sheet can include no more than 100 distinct cross-sheet references.

So there is no account-wide view of them. Nothing tells you, when a sheet is deleted or a colleague is unshared, which of your other sheets were reading it — you find out when somebody opens a sheet and notices a column of errors, which on a sheet nobody opens often means months later.

Audit cross-sheet references across your sheets

Our cross-sheet reference audit asks Smartsheet’s API for the references on up to 1,000 accessible sheets per run and lists each one with the status it returned and the source sheet it reads, so the unresolvable ones can be reviewed together. You can search by source sheet to see which of your sheets were reading a particular one, and export the results as CSV. It is read-only: it never creates, repoints or deletes a reference, and repointing is still done in Smartsheet on the sheet holding the formula. Review the scan’s coverage and any sheets it could not read before treating the list as complete.

The API reports a status vocabulary of its own, which is not the same wording as the manager’s filters. Four of its values are documented as unresolvable: BROKEN, “the data source location (column, row or sheet) was deleted”; CIRCULAR, “the formula reference is self referencing and cannot be resolved”; BLOCKED, “a reference is downstream of a circular issue”; and NOT-SHARED, “no common shared users”. Two more are documented as temporary and are not faults in your sheet: INVALID/UNKNOWN is “the reference is new and had not been validated”, and DISABLED is “updating the reference is temporarily disabled due to maintenance”. The audit counts and lists those two apart from the broken ones, because chasing a formula that is fine is its own waste of an afternoon.

Official Smartsheet documentation: View, modify or delete cross-sheet references, Cross-sheet reference limits, Formula error messages, Reference status values.

Do it in one pass instead

Check cross-sheet references on up to 1,000 sheets per run and review the ones Smartsheet reports it can no longer resolve, with the status it returned.

Try Reference Audit Free

Frequently asked questions

Why does my Smartsheet formula show #REF?

Smartsheet documents #REF as a formula pointing at a cell that does not exist, which can happen after a row or column is deleted. Where the formula reads another sheet, check the cross-sheet reference it uses: right-click a cell, choose Manage References, and filter to Broken, which means the source rows, columns or cells were deleted. A different error — #INVALID REF, #CIRCULAR REFERENCE, #BLOCKED or #PERMISSION ERROR — points at a different cause.

What is the difference between #REF and #INVALID REF?

#REF means the formula points at a cell that does not exist. #INVALID REF means the name inside the curly braces does not match any existing cross-sheet reference — typically because the name was mistyped, or because the reference was deleted, which also removes it for everyone else who can access the sheet.

Is a reference with an unvalidated or disabled status broken?

No. Smartsheet documents INVALID/UNKNOWN as a reference that is new and had not been validated, and DISABLED as updating being temporarily disabled due to maintenance. Neither indicates a problem with your sheet, so they are worth separating from BROKEN, CIRCULAR, BLOCKED and NOT-SHARED before you start opening formulas.