Get value from the cell above
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.
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.
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