Get value from the cell above

Question

Is it possible in Google Spreadsheets to reach the value of the cell just above?

Meaning: In one cell A2 I want to display the value from the cell above, that is A1. Not because it is that coordinate, but because it is the cell above. I do this by simply setting the value equal to the above cell:

If I create a new row in between those two, I get this:

As we know, no change in the values, since the reference is not relative in this way. How can I make it relative to the cell, so I always pick the cell above nomatter what is changed? I want the result to be this:

The term relative is wrong in the case of course, but I hope you get my point. I both want the usual relative behavior (so I can move the cell itself and its reference will fit to the new coloumn and row) as well as the behavior that the reference does not point towards a specific fixed coordinate but rather a specific path from the cell itself.

Artimas

Answer 1

You can address it using the following function:

=INDIRECT(ADDRESS(ROW()-1;COLUMN()))

COLUMN() returns a numeric reference to the current column

ROW() returns a numeric reference to the current row.

In the example here, subtracting 1 from the row gives you the previous row. This math can be applied to the ROW() and/or the COLUMN(), but in answering your question, this formula will reference the cell above.

Then we have ADDRESS() which accepts a numeric row and column reference and returns a cell reference as a string.

Finally INDIRECT() allows you to pass a cell reference in as a string, and it processes it as a formula.

Google Spreadsheets give you help hints as you type, so you should get a full explanation of each function as you type the formula above in.

Lalla

Anyone know how to get all previous cells? – Ellerey

@SebastianThomas you could use JOIN and put the reference of precious cells. Previous cells reference could be as what is described in this answer. Boom, ultimate flexibility, but with that, comes complexity. – C

@SebastianThomas =SUM(A1:INDIRECT(ADDRESS(ROW()-1,COLUMN()))) works for me. – Olympian

Genius, thank you. I needed a way for week numbers and dates to stay consistent while allowing sorting and ordering of the events, works perfectly. – Runyan

=INDIRECT(ADDRESS(ROW()-1;COLUMN())) – Brawley

Answer 2

For anyone who stumbles across this question, you can also specify the column by doing something like this:

=INDIRECT("A" & ROW()-1)

This comes in handy if you are returning values in Column B but checking against the previous row in Column A.

Perdurable

This was exactly what I needed to get the difference between the current row and the previous row: =H:H-INDIRECT("H"&(ROW()-1)). Thanks! – Cranial

It does break when I have merged cells, how do I acheive? – Heterodyne

My apologies. I don't know how to do this with merged cells. My recommendation is to avoid using merged cells at all costs. They mess up column sorting, filtering, pivot tables, macros, and formulas like this. An alternative that works way better than merging cells is formatting the cells, going to the alignment tab, and selecting "Center Across Selection" in the Horizontal dropdown. – Perdurable

Answer 3

The shortest, and easier for VisiCal old timer is the old RC syntax with relative values…

=INDIRECT("R[-1]C[0]"; FALSE)

Very visual, simple code template to remember and modify, and very short.

Regards, Antoine

Gabriel

+1 for adding an alternative way to do it not mentioned even in the official reference pages – for me the most readable option. Thanks! – Ablation

Answer 4

This works too, and seems to be the intended, more direct way:

=OFFSET(A2;-1;0)

A2 must of course be edited to the cell to relate from, typically the cell entered in. After that copy/pasting adjusts as usual.

A range can be specified with 2 more parameters.

Menstrual

Answer 5

This can be done using the OFFSET function. Here is a Google Sheets documentation about OFFSET.
https://support.google.com/docs/answer/3093379?hl=en

Syntax: OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

• cell_reference - The starting point from which to count the offset rows and columns.

• offset_rows - The number of rows to shift by.

  • offset_rows must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

• offset_columns - The number of columns to shift by.

  • offset_columns must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

• height - [OPTIONAL] - The height of the range to return starting at the offset target.

• width - [OPTIONAL] - The width of the range to return starting at the offset target.

Here is an example:

+---+----+----+----+----+----+
|   | B  | C  | D  | E  | F  |
+---+----+----+----+----+----+
| 2 |  2 |  4 |  6 |  8 | 10 |
+---+----+----+----+----+----+
| 3 | 12 | 14 | 16 | 18 | 20 |
+---+----+----+----+----+----+
| 4 | 22 | 24 | 26 | 28 | 30 |
+---+----+----+----+----+----+
| 5 | 32 | 34 | 36 | 38 | 40 |
+---+----+----+----+----+----+
| 6 | 42 | 44 | 46 | 48 | 50 |
+---+----+----+----+----+----+
| 7 | 52 | 54 | 56 | 58 | 60 |
+---+----+----+----+----+----+

Reference Cell: D4

+---------------------+----+
| `=OFFSET(D4,-2,-2)` |  2 |
| `=OFFSET(D4,-2,-1)` |  4 |
| `=OFFSET(D4,-2,0)`  |  6 |
| `=OFFSET(D4,-1,-2)` | 12 |
| `=OFFSET(D4,-1,-1)` | 14 |
| `=OFFSET(D4,-1,0)`  | 16 |
| `=OFFSET(D4,0,-2)`  | 22 |
| `=OFFSET(D4,0,-1)`  | 24 |
| `=OFFSET(D4,0,0)`   | 26 |
| `=OFFSET(D4,0,1)`   | 28 |
| `=OFFSET(D4,0,2)`   | 30 |
| `=OFFSET(D4,1,-2)`  | 32 |
| `=OFFSET(D4,1,-1)`  | 34 |
| `=OFFSET(D4,1,0)`   | 36 |
| `=OFFSET(D4,1,1)`   | 38 |
| `=OFFSET(D4,1,2)`   | 40 |
| `=OFFSET(D4,2,-2)`  | 42 |
| `=OFFSET(D4,2,-1)`  | 44 |
| `=OFFSET(D4,2,0)`   | 46 |
| `=OFFSET(D4,2,1)`   | 48 |
| `=OFFSET(D4,2,2)`   | 50 |
+---------------------+----+

Pneumatics