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:

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.

score 222 · Accepted Answer · edited Feb 08 '23 at 15:07

222

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.

edited Feb 08 '23 at 15:07

Elton Santana

950
3
11
22

answered Oct 03 '14 at 12:35

Aaron

2,344
1
11
2

Thank you for a great and well-explained answer. It works perfectly. – Steeven Oct 03 '14 at 12:45
2

Anyone know how to get all previous cells? – Sebastian Thomas Jan 13 '17 at 07:26
6

This answer changed my life. Thank you. – dancow Jun 30 '17 at 06:23
1

@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. – Mohammed Joraid Jun 12 '18 at 15:06
4

@SebastianThomas `=SUM(A1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))` works for me. – Jarett Millard Jul 03 '18 at 20:39
1

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. – Maurice Jul 18 '18 at 23:07
6

=INDIRECT(ADDRESS(ROW()-1;COLUMN())) – xpeiro Feb 14 '20 at 20:17

score 35 · Answer 2 · answered Oct 24 '16 at 22:23

35

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.

answered Oct 24 '16 at 22:23

user2597747

693
6
9

1

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! – nocdib Aug 24 '18 at 16:48
It does break when I have merged cells, how do I acheive? – Mohith7548 Apr 20 '23 at 06:39
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. – user2597747 Apr 21 '23 at 22:36

score 5 · Answer 3 · answered Oct 09 '21 at 17:15

5

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

answered Oct 09 '21 at 17:15

Antoine Beaubien

106
1
5

Get value from the cell above

3 Answers3

Linked