DONT'T CHANGE MY FORMULA!!!!!!!
How about keeping the formula intact, returning a #REF result and NOT editing the formula with a #REF comment. This stupid idea prevents one from following the formula back and evaluating what the error is, or what cell it's referencing. Who ever thought of doing this should have some training! If one receives a spreadsheet developed by someone else, they may not know how the formulas work/reference, but leaving the formula intact gives the opportunity to understand what the result should be. CHANGING the formula should NEVER be an automatic option by the software!!!!!
1 comment

Harlan Grove commented
If your formulas in cells X99 and Y100 were =A5 and =B6, respectively, and you CUT cell B6 and pasted it into cell A5, you'd want both the X99 and Y100 formulas to be =A5? If Y100 were returning the correct value of the former B6 but now A5 but cell X99 returns #REF!. How would you be able to diagnose the problem in cell X99? If you made X99 the active cell, pressed [F2] to enter Edit mode, then pressed [Enter] to reenter it without changing anything, would you expect it still to return #REF! or the new value in A5?
Likewise for deleting row 5 or column A.
In any of these actions, you're DESTROYING a range to which your formulas refer. I'd argue it's better to have Excel replace the A5 reference with #REF!. It'd be better still if Excel displayed a confirmation dialog stating that you're about to destroy a range to which other cells refer and prompting you whether you really want to do that. However, if you did perform the action, Excel should alter the formulas. Maybe another approach would be Excel adding the original formula as a cell comment. However, the formula itself should be changed to reflect the fact that you've destroyed a formerly valid reference.