Google Sheets has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to point to the new cell location.
I'd like to be able to cut and paste my data without my formulas changing.
I've done some hideous things in the past involving the INDIRECT()
function to work round this feature, but it seems like there's got to be an easier way. Any suggestions?
Best Answer
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
, e.g.=INDIRECT("$A$1")
or even just=INDIRECT("A1")
.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:
=A1
and autofill)=A1
,=A2
...=A99
in the=INDIRECT("A1")
etc. Note you have the option to use regular expressions when doing a find-and-replace.