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:
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.
Answer
You can address it like this:
=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.