CODEWITHSUNDEEP
referencegoogle-sheetsrelative-path
Steeven
Steeven

Reputation: 4200

Get value from the cell above

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:

enter image description here

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

enter image description here

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:

enter image description here

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.

Upvotes: 109

Views: 127121

Answers (5)

DxTx
DxTx

Reputation: 3357

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: Values

+---+----+----+----+----+----+
|   | 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 |
+---------------------+----+

Upvotes: 0

Bruno Mailly
Bruno Mailly

Reputation: 11

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.

Upvotes: 1

Aaron
Aaron

Reputation: 2574

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.

Upvotes: 245

Antoine Beaubien
Antoine Beaubien

Reputation: 126

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

Upvotes: 7

user2597747
user2597747

Reputation: 713

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.

Upvotes: 37

Related Questions