WRITTEN BY: supportmymoto.com STAFF
Concerning the #REF! error
The #REF! error happens when a reference is invalid. In lots of instances, it’s because sheets, rows, or columns have been eliminated, or as a result of a method with relative references has been copied to a brand new location the place references are invalid. Within the instance proven, the method in C10 returns a #REF! error when copied to cell E5:
=SUM(C5:C9) // unique C10 =SUM(#REF) // when copied to E5
Stopping #REF errors
The easiest way to stop #REF! errors is to stop then from occurring within the first place. Earlier than you delete columns, rows, or sheets make sure they are not referenced by formulation within the workbook. In case you are copying and pasting a method to a brand new location, it’s possible you’ll need to convert some cell references to an absolute reference to stop adjustments in the course of the copy operation.
In case you trigger a #REF! error, it is best to repair instantly. For instance, when you delete a column, and #REF! errors seem, undo that motion (you need to use the shortcut Management + Z). Once you undo, the #REF! errors will disappear. Then edit the method(s) to exclude the column you need to delete, transferring knowledge if wanted. Lastly, delete the column and ensure there are not any #REF! errors.
Clearing a number of #REF! errors
To shortly take away many #REF errors from a worksheet, you need to use Discover and Change. Use the shortcut Management + H to open the Discover and Change dialog. Enter #REF! within the discover enter space, and go away the exchange enter space clean:
You possibly can then make case-by-case adjustments with Discover subsequent + Change, or use Change All to switch all #REF errors in a single step.
Fixing #REF errors
#REF errors are considerably tough to repair as a result of the unique cell reference is gone perpetually. If you realize what the reference needs to be, you’ll be able to merely repair it manually. Simply edit the method and exchange #REF! with a sound reference. If you do not know what the cell reference needs to be, you will have to check the worksheet in additional element earlier than you’ll be able to restore the method.
Be aware: you’ll be able to’t undo the deletion of a sheet in Excel. In case you delete a worksheet tab, and see #REF errors, the best choice might be to shut the file and re-open the final saved model. For that reason, all the time save a workbook (or save a replica) earlier than deleting a number of sheets.
#REF! errors with VLOOKUP
You may even see a #REF! error with the VLOOKUP perform, when a column is specified incorrectly. Within the display under, VLOOKUP returns #REF! as a result of there is no such thing as a column 3 within the desk vary, which is B3:C7:
When the column index is about to the right worth of two the #REF! error is resolved and VLOOKUP works correctly:
Be aware: you may additionally see a #REF error with the INDEX perform when a row or column reference just isn’t legitimate.
Trapping the #REF! error with IFERROR
Normally, it would not make sense to lure the #REF! error in a method, as a result of #REF! signifies a low-level downside. Nonetheless, one state of affairs the place IFERROR might make sense to catch a #REF error is when you find yourself constructing references dynamically with the INDIRECT perform.