The =$A$1 notation can also be automatically moved if the value in (say) A1 is changed.
The only surefire solution is to enclose all your references in
=INDIRECT("$A$1") or even just
The difficulty with either of these solutions is where you have many of the same formula, e.g.
Normally, you would fill in the first
=A1 and then copy-and-paste (or drag down using autofill) to fill out the consecutive formula automatically. If, however the first formula is
=$A$1 or =INDIRECT("A1") then this trick won't work (in fact, this is what
$ is for - to prevent it being automatically changed).
If you have many consecutive formula, one solution is:
- Create the formula as you normally (use plain
=A1 and autofill)
- Use the replace dialog (Ctrl-H) and use "search in formulas" to wrap all
=A99 in the
=INDIRECT("A1") etc. Note you have the option to use regular expressions when doing a find-and-replace.