Return values from the row above to the current ro

Question

I am trying to do simple thing: I just need to set some cells to values that are on the previous rows. I have tried =A(Row()-1) but it does not work.

Answer 1

Easier way for me is to switch to R1C1 notation and just use R[-1]C1 and switch back when done.

Answer 2

To solve this problem in Excel, usually I would just type in the literal row number of the cell above, e.g., if I'm typing in Cell A7, I would use the formula =A6. Then if I copied that formula to other cells, they would also use the row of the previous cell.

Another option is to use Indirect(), which resolves the literal statement inside to be a formula. You could use something like:

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

The above formula will resolve to the value of the cell in column A and the row that is one less than that of the cell which contains the formula.

Answer 3

You can also use =OFFSET([@column];-1;0) if you are in a named table.

Answer 4

This formula does not require a row letter reference ("A", "B", etc.). It returns the value of the cell one row above in the same column.

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

Answer 5

I followed BEN and Thanks and Lot for the Answer,So I used his idea to get my solution, I am posting the same hence if some one else has similar requirement, then you also can use my solution as well, My requirement was something like I want to get the sum of entire data from the first row to the last row, and I was generating the spreadsheet programmatically so don't and can't hard code the row names in sum as the data is always dynamic and number of rows are never constant. My formula was something like as follows.

=SUM(B1:INDIRECT("B"&ROW()-4))