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.

 

[Joecool025]

Oops, that's formula (Offset) and link:
https://youtu.be/L_rRC9plsTU

 

[MARK858]

Offset is also volatile so has the same issues as Indirect.