Google-sheets – How to make a formula dynamically relating to a cell on “the” left in Google Spreadsheet

google sheets

Say I have two a table like this:

   Name    B    C    D
1  Joe     15  17  =B1-C1
2  Mike    25  22 (auto fill)

And I need to insert a column between C and D like this:

   Name    B    C    D      E
1  Joe     15  17   31    =C1-D1
2  Mike    25  22   34   (auto fill)

And I want the last column change to C1-D1. Every time I need to fill in myself. Is it possible to just say:

My value = My 2nd left cell – My left cell

Best Answer

Use offset, with the base point being the same cell in which you enter the formula. So, in D1 it would be

=offset(D1, 0, -2) - offset(D1, 0, -1)

When a new column is inserted between C and D, the above becomes

=offset(E1, 0, -2) - offset(E1, 0, -1)

which still has the meaning "My 2nd left cell - My left cell".

Related Solutions

Google-sheets – How to select all cells between two cells, where more may be inserted later

As Rubén hinted in a comment, the cell range reference cell1:cell2 will be automatically updated if you insert or delete cells within the range. For example: if you have a formula

=countif(A2:A10, "<>")

and insert a row between 2nd and 10th, the command will automatically change to

=countif(A2:A11, "<>")

Google-sheets – Filter Google Spreadsheet Array Formula

I believe the issue is caused by filtered view sorting the column that contains input to a function. It can be reproduced in the following minimal example:

+---+-------+---+
|   |   A   | B |
+---+-------+---+
| 1 | input | 1 |
| 2 | 5     |   |
| 3 | 4     |   |
+---+-------+---+

where B1 is the formula =A2-A3. Create a filtered view for cells A1:B3 and use it to sort column A in ascending order. The result: B1 will display #REF error

The formula in cell A2 is referencing a range in the active filter that is not in the same row.

I can't really explain why this fails: my guess is that the logic of filtered view does not handle remapping cell references within the view.

But I can suggest an alternative approach: use FILTER and SORT, possibly putting results on different sheets for ease of sharing or reading. For example,

=SORT(FILTER(A2:A15, B2:B15=5), 1, TRUE)

keeps only the entries from A with 5 in column B, and presents them in ascending order. This formula could appear in another column (C,...), or on a different sheet.

Best Answer

Related Solutions

Google-sheets – How to select all cells between two cells, where more may be inserted later

Google-sheets – Filter Google Spreadsheet Array Formula

Related Topic