Preventing #REF! Errors when deleting rows

[excelpinto]

Ok, so my users won't listen to me when I tell them to clear out rows instead of deleting them which leaves me with fun #REF! errors in formulas that referenced said rows. My question is there away to stop excel from doing that?

I'm looking for a way to lock down certian cells that contain formulas so that they never change, even if the target cell is deleted.

My current workaround is to just have a macro run and populate those cells with the correct formulas again, but is there a better way? Thx.

 

[Lewiy]

If the target cell is deleted, how do you expect the formula to work??

 

[excelpinto]

If the target cell was deleted, I wouldn't expect the formula to work. What I'm looking for is a way to stop excel from automatically flipping all my missing cell references to #REF!. I would like the formulas to remain static no matter what happens to the cells they may reference.

I have 2 sheets in my workbook, 1 sheet is data imported from various sources, the other is a statistics page that presents stats about the data. Users sometimes delete all the rows on the data sheet which breaks all my formulas on the stats page. Reimporting the data is easy, but the references are all gone.

I'd like for a way to allow users to be able to manipulate and delete rows at will on the data sheet without having to worry about this or having to run a macro that just plugs back in my formulas every time.

 

[Oaktree]

What if you just don't allow them to delete cells? Press CTRL+A to select all of the cells, go to format --> cells --> protection and uncheck "locked". Then, use tools --> protection --> protect sheet and check everything except "delete rows" and "delete columns".

 

[gardnertoo]

Another (ugly) solution is to use INDIRECT. Instead of =SUM($A$5:$A$7) which shrinks or crashes with row deletions, you could use =SUM(INDIRECT("$A$5:$A$7")) which will sum whatever ends up in A5:A7 after deletion.

 

[excelpinto]

Hmm, thx for the input guys.

I'll check out the indirect function, which, according to this page http://office.microsoft.com/en-us/excel/HP052091391033.aspx seems like it may work out. If that fails, ill just lock the sheet down like Oaktree suggests.

Oh and gardnertoo, you suggested that the indirect is an 'ugly' approach. Is that purely because you'd have to update all of your formulas to use indirect or does indirect not work in some situations?

 

[gardnertoo]

My chief objection to the INDIRECT function is this: if I need to troubleshoot the formula =SUM($A$5:$A$7), when I click on it in the forumla bar, I get a colored box around the range A5:A7. With the INDIRECT function, clicking on the cell gets you nothing.

 

[JazzSP8]

It's probably also worth mentioning that INDIRECT is a volatile function, which basically means if your going to be using that approach a lot then your going to suffer for it in recalculation times...

More information here;

http://www.decisionmodels.com/calcsecretsi.htm

It can work for you, I use it on a couple of worksheets where rows can get deleted because of a macro...

 

[cboshdave]

Ok, so my users won't listen to me when I tell them to clear out rows instead of deleting them which leaves me with fun #REF! errors in formulas that referenced said rows. My question is there away to stop excel from doing that?

I'm looking for a way to lock down certian cells that contain formulas so that they never change, even if the target cell is deleted.

My current workaround is to just have a macro run and populate those cells with the correct formulas again, but is there a better way? Thx.

I know this is an old post. I was trying to find a solution to the same problem and I found a solution that works for me. The real issue is that your formula references the row you need to delete. This causes your #REF result. In my case, I needed to compare the value of the cell on the current row (one cell left) to the value of the cell above it (one cell left and one cell up). If the values are the same, I delete the second row. So, I use a formula like this: =IF(OFFSET(B1,0,-1) = OFFSET(B1,-1,-1), "X", "")
Offset does not directly reference the other row so when it is deleted the formula adjusts properly without breaking.

 

[Joecool025]

Hi All,
I found the perfect solution to this problem, and it's MUCH simpler and pain free than all that.

Check this 3 min Youtube video out. The solution is using the formula "Offset)

Big time thank you to ExcellsFun for this solution. I have already implemented the fix and can tell you that it works like a charm!

All the best!