'How do I stop excel formulas from changing?
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.
Solution 1:[1]
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.
Solution 2:[2]
You could use (non-volatile) INDEX =INDEX(Sheet2!$1:$1048576,2,1)
Solution 3:[3]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Skin |
| Solution 2 | P.b |
| Solution 3 | Solar Mike |