Q. My formula suddenly started giving me #REF! errors. What went wrong?
A. Technically speaking, something in the formula refers to a value in a location it can’t find. If your formula refers to values in another workbook that have been moved to a different location or deleted, a #REF! error will result.
One way to start sleuthing the mystery is to use Trace Precedents. With your problem cell selected, on the Formulas tab, Formula Auditing group, click the Trace Precedents button. Double-click on the arrow head or line to start navigating to the various components in your formula.
If one of your components shows #REF! in the cell reference, it can no longer find the workbook or work sheet that this component referred to. If the problem is a different workbook, you can also refer to the Data tab, Connections group, Edit Links button. The button only appears if your workbook contains external links.