I have the following formula: =Sheet2!A2. When I add a column at the start of Sheet2, the formula automatically changes to =Sheet!B2 (I tried adding $ signs but no luck), how can I prevent this? This also happens to me when I use Sheet2 data in a graph.

CodePudding user response：

The only thing I can think of is doing something like using the INDIRECT function ...

=INDIRECT("Sheet2!A2")

... it definitely won't move but it is a fairly heavy way to achieve what you want.

CodePudding user response：

You could use (non-volatile) INDEX =INDEX(Sheet2!$1:$1048576,2,1)

CodePudding user response：

Or use an if() with a cell to trigger on/off:

Cell A1 set to “off” and:

In cell B2:

=if(A1="off","",Sheet!B2)

If A1 is set to on then it will reference cell b2 on the named sheet.