Personally, I would protect the cells so they can't do that to you. However, since you asked.... yes there is a way.
When you put a formula in a cell referencing another cell, use the $ in the cell reference. For example, fill a small sheet with this data:
A1 = 0
A2 = 0
A3 = 1
A4 = 0
A5 = 0
In cells B1 through C5, put any numbers so we can total them across in column D.
In cell D1, you place the formula:
=IF(A1=0,B1+C1,"nada").
When that cell is "dragged" down, the cell references change to the new row references...
(in D2) =IF(A2=0,B2+C2,"nada")
(in D3) =IF(A3=0,B3+C3,"nada")
etc..... all the way down.
However, if you use an absolute reference and place THIS formula in cell E1 and drag it down....
=IF($A$1=0,B1+C1,"nada")
It will change to:
(in D2) =IF($A$1=0,B2+C2,"nada")
(in D3) =IF($A$1=0,B3+C3,"nada")
etc... all the way down.
Now, try copying cell E1 to cell F10. You'll see that the $A$1 remains unchanged but the reference to adding column B and C changed to column C and D, both row 10. To prevent this, you simply use 1 $ sign in front of the column designation...
Change cell E1 to read:
=IF($A$1=0,$B1+$C1,"nada")
then drag it down.
You see the values in column E didn't change, which is good! ;)
NOW... copy cell E1 and paste it in cell F10. BINGO!!! Even though there is nothing in column B or C to add, the column reference remains intact adding column B and C and NOT columns C and D like it did before.
The same can be done to prevent the rows from changing ....
In cell F1, type this:
=IF($A$1=0,B$1+C$1,"nada")
now drag it down. Note, the formulas do NOT change one little bit. It copies exactly... going down that is... now drag F1 to the right and see what happens.
So, you just have to play with your formulas... if you don't want the cell reference to change even if the cell is copied to another locations... use the $