Protect me from #REF! ambushes
Every so often I delete cells and unbeknownst to me create a bunch of #REF! errors in another part of my worksheet. I may not realize until much later when I can no longer undo back to fix the error. Excel could spare me this. If my deletion is about to create #REF!s it should bring up an alert like "Other cells depend on the cells you are about to delete. Would you like Excel to adjust their formulas to avoid #REF! errors? [Yes][No][Cancel]. [No] would do what it does today. [Cancel] would abort the operation. [Yes] would smartly substitute the constant or formula of the deleted cells (whichever applies) into the formulas of the cells that would end up with #REF.
Example: let's say I am about to delete B1 which holds the constant 10%. As a side effect I am about to trash the formula for C1, =A1B1. If I go ahead and say [Yes], Excel should just change the formula for C1 to =A110%. Presto, carnage avoided. The same substitution logic works if the deleted cells contain formulas instead of constants.
This would also be a mighty handy tool to simplify models. I often create extra rows or columns to help me do intermediate calculations, though I don't really need them and they bloat my model. I end up hiding them but they are still there. Wanna get rid of them? No problem, just delete the row or column and let Excel use this feature to fold their formulas into the formulas of the cells that depend on them.
RH
shared this idea
Thanks to RH for posting this suggestion. If avoiding #REF! errors is important to you also, please add your vote to support RH’s suggestion. We will prioritize accordingly.
Thanks,
Steve (MS Excel)
7 comments
-
Joe
commented
The thing is that part of functionality described in an OP post is already there: trace precedents/dependents.
It just needs to combined with cell deletion event...
But i really like the idea of not only warning you about reference but also allowing you to replace old range with a new one in the same window so you don't have to do a following:
1. Delete range.
2. Got warned that there's a references.
3. Go to range with formula
4. Change references.
5. Go back to p.1 and delete a range.Instead
1. Delete range.
2. Got warned that there's a references.
3. Put new range in this "warn" window."Smart" adjustment like described in OP post is highly undesirable because lead to implicit side effects.
-
Kevin Osborn
commented
An improvement to the suggestion would be to list the cells where the #REF would occur similar to Find All so that the user can click on the list and change the formula that would result in #REF and then come back to delete the cell or make the changes that would cause the #REF.
-
Anonymous
commented
I really like the idea of being warned before creating a bunch of #REF errors!!! (I've sometimes ended-up having to trash whole workbooks and beginning from scratch after such disasters) And I agree we should be warned about any graphs or names affected (since dependency doesn't seem to help there)
...but I'm not sure about the "adjust" part of the suggestion. I don't like the idea that Excel would then make changes that might go unnoticed and be inappropriate in some cases... What I would like, is to be able to either "adjust all" (like suggested) or "Choose adjustments’" where I could then go through the suggested changes for each distinct formula/range in each sheet… At least, choosing to “adjust” should create a "log" (cell/range/graph/name affected | value before | value after) of the changes made that could be reviewed.
-
Raja Lakshman
commented
Please see suggestion in
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/19597927-need-ability-to-allow-only-paste-values-in-protectHope you all can vote for that.
-
ms@baccma.co.uk
commented
But ONLY if you allow it to be turned off. In order to avoid the rather confusing dependency arrows, I use the #REF error constructively. If I want to see what a row does quickly, I delete it, look for where the #REF errors occur, then undo the delete.
It would be rather irritating if I got warning messages when doing this, particularly if the warning message was modal.
-
Gary
commented
Good idea. The warning should also cover any references to the range name that the cell(s) might contain.
(I'm out of votes, so can't support this idea.)
-
Kenneth Barber commented
Such a feature would definitely be useful. I should add that we are not just talking about #REF! errors in cells, but also in graphs and names.
The current workaround is to go Formula tab, Trace Dependents on any cell that you are about to delete. Even then, it only goes to cells.