Specific errors: Missing References

I mentioned earlier that one very common error code in spreadsheets is what I’ll call the “missing reference error”. In this post I’ll cover the definition of the error, what it looks like in various spreadsheet programs, and some techniques for fixing it.

Definition
When you have a reference to a cell, and then you delete that cell, you’ll wind up with a missing reference.  That’s pretty much all there is to it.

Appearance
One of the easiest ways to recognize a missing reference is the fact that there will be an error showing up in both the cell and in the formula bar. Usually.

Unfortunately if you have named references then many programs will still consider the name valid, and show it, but what would normally appear in the formula bar for a non-named reference will now only appear in the definition of the name. My advice on names is starting to feel like Dr. Who when he said something like “You should always pick up things you find. You never know what will be useful. Oh, and you shouldn’t keep too much in your pockets. It makes it hard to find things.” Ah, well, moderation in everything. In any case, if you see the cell part of the error in the appropriate location, but the formula seems normal, check the definitions of any named variables you have!

In Excel 97 & 2007:

  • Cell: #REF!
  • Formula bar: #REF! where the reference used to be. (Example: =#REF!+1)

In Google Spreadsheet:

  • Cell: Results of the formula without the reference OR, if the formula consists of a missing reference, #REF!.
  • Formula bar: {} where the reference used to be. (Example: ={}+1)

In OpenOffice Calc:

  • Cell: #REF!, Err:509, or Err:522
  • Formula bar: #REF! over the part of the reference that’s missing. (Example: =A#REF!+1, =#REF!1+1, or =#REF!#REF!+1)

In Microsoft Works Spreadsheet:

  • Cell: ERR
  • Formula bar: ERR where the missing reference would be. (Example: =ERR+1)

Repair
So, now that you’ve got a “missing reference error”, and you know what it means. By the time you’ve figured out that you’ve got the error, you’ve also figured out what part of the formula is missing. So, once you know all that, how do you fix it?

Unfortunately, this is a pretty bad error. The only way to know for sure what’s missing is to undo it (ctrl-z in most programs). Unfortunately if the reference was in a worksheet you deleted, there’s often no way to “undo” that deletion.

If it’s not possible to “undo” then you can look at the context, and try to remember or guess what it used to be. If it wasn’t important you can just delete the part of the formula that’s causing the error. Problem solved!

If, however, it was important then there’s only a few options. If you moved the data somewhere else then you can update the reference. If you deleted the data by accident, you can try to recreate it. Failing that, the only option is to restore from an older version of the file, if you have one. This is where Google Docs really shines: it has a complete revision history, so you automatically have a backup. You’ll lose all the edits you made in the meantime, but it’s easier to re-create them since you can look up what they were.

If you’re not working with Google Docs and you’re working with the only copy of a mission-critical spreadsheet, I highly recommend you consider getting some version control software.