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?

score 45 · Accepted Answer · answered Oct 16 '15 at 20:37

45

Yes.

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

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

answered Oct 16 '15 at 20:37

Jonatan Littke

5,583
3
38
40

This isn't the accepted answer but worked for me (so I upvoted). – Max Aug 16 '18 at 11:59
This doesn't work for the given question. If adding a column in the Sheet 'Two', the reference will be updated. – Daniel Rodríguez Oct 06 '21 at 02:29

score 4 · Answer 2 · answered May 13 '20 at 12:34

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.

score 1 · Answer 3 · edited Mar 14 '21 at 00:21

1

=INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4,1,"One"))

edited Mar 14 '21 at 00:21

Jin Lee

3,194
12
46
86

answered Mar 13 '21 at 11:34

Tevfik Alkan

21
1

score 1 · Answer 4 · answered Oct 06 '21 at 02:28

1

Indirect will returns a cell reference specified by a string. This way when a new column is added to the left of B in Sheet 'Two', you will reference the new B and not the old one (new C)

A1 = INDIRECT("Two!B1")

answered Oct 06 '21 at 02:28

Daniel Rodríguez

548
1
10
30

Static cell reference google spreadsheet

4 Answers4