First, make sure that all cells on the sheet EXCEPT where you want to accept
data entry from the user are Locked. This will keep them from being able to
drag a precedent cell into another (Locked) cell.
If a user drags a precedent cell into an empty unlocked cell, the formula in
the Locked cell does indeed change to show the new reference. But the
formula itself has not changed, just the references to the precedent data,
which the user has just redefined.
Other conditions create other results. Consider this layout: Cells A1:B2
are unlocked, Cell C1 = A1+B1, cell C2 = A2 + B2 and are locked. Cell A3 is
unlocked, but not a precedent for anything, just unlocked and empty. The
sheet is protected.
Trying to drag C1 or C2 to any location results in a "cell you are
attempting ... locked" and you are prevented from doing that.
You can drag any ONE of A1:B2 into A3 and the associated formula will be
altered to reflect the move. i.e., you move B1 into A3, C1 changes from
=A1+B1 to =A1+A3 at this point you could even move B2 up to B1 (left empty by
the previous move) and the formula in C2 would become =A2+B1, again an
alteration reflecting the user directed relocation of precedent data.
However, there are some things that wouldn't work. Go back to the original
setup where C1=A1+B1 and C2 = A2+B2 and try dragging A1 into A2. You'll get
a "replace contents?" prompt, and if you continue and say YES, then the
formula in C2 suddenly changes to REF# because you've deleted the previous
content of A2.
I suppose in one fashion this could be looked at as having changed the
formula, but it does follow the general scheme of the way Excel works when
moving references around. Your first line of defense to prevent this is to
only unlock cells where the input comes from an 'outside' source, such as the
user and must be unlocked to provide functionality.
I don't see this as being a huge problem. If you move it accidentally
during design and tell the user to enter something specific in A1 (having
accidentally dragged that to A2) and nothing happens: well, you should have
caught that during final testing before release. If the user makes such a
change, woe be unto them. In over a decade of providing Excel solutions
professionally, I don't believe I can recall a single instance of where an
end user did such a thing - at least not and then contact me asking "what
happened - your application turned to crud just out of the blue on me!".
