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 consequutive 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 consequative formula, one solution is:
- Create the formula as you normally (use plain
=A1 and autofill)
- Import "Search And Replace Formulas" Google App Script
- Make judicious use of find-and-replace to automatically wrap all
=A99 in the
=INDIRECT call. (I used a custom version of this script with