Static cell reference google spreadsheet

Question

I have a two sheets "One" and "Two"

I want column A in "One" to reference column B in "Two" the following works fine up to a point.

A1 = Two!B1

Then I use the fill to populate the rest of the column. The problem is in Two I continually want to add a column to the left of B which becomes the new B. I want A1 to reference the new B1, but spreadsheets automagically updates all reference in One to C instead of B. Is there a way to force B to remain static?

Answer 1

Yes.

Use $A$1 to make the reference remain static.

Read more here: http://www.gcflearnfree.org/googlespreadsheets/14.3

Answer 2

TLDR;
A1 is a reference relative to the current cell. Copy-paste will update this reference.
$A$1 is a static reference to a specific cell. Copy-paste will not change this reference.
Relative and Absolute References in Google Sheets and Excel.

Spreadsheets do the heavy lifting for you, by defaulting cell references in formulas as a "relative" to the current cell.
To override this default behavior, Use a $ sign to keep the reference "static" when pasting the formula into other cells.

For example, if you are in cell A1 and the formula in that cell references B1, then if you copy-paste this formula into cell A2, the formula actually pasted in the cell references B2. This is generally what we want.

However, if you are in cell A1 and the formula in that cell now says $B$1 (it will still reference the cell B1, as before), but
then if you copy-paste this formula into cell A2, the formula actually pasted in the cell will still say ($B$1) (and still reference the cell B1).

You can also use "mixed" references, where only the column or row is static, and the other is a dynamic reference. For example $B1 or B$1. This link gives a good example and use case (creating multiplication tables) for mixed references, as well as static vs relative references, and warns to always use static references with vlookup and arrays: Relative and Absolute References in Google Sheets and Excel.