Apache OpenOffice Community Forum

I am trying to find a solution for the following situation: in a given cell, I want to obtain the value in the cell in the row above it. I was thinking of using the OFFSET function, specifying a row offset of -1 and a zero column offset. However, I don't want to hard-code the first offset parameter. I am looking for a way to tell OFFSET to use the current cell. Something like:
=OFFSET(<current-cell>,-1,0) - where <current-cell> is determined by a function call (or some other fashion)
So, if the formula is in cell A5, <current-cell> would resolve to A5.

The few discussions I have found were oriented around someone wanting to write a macro to perform some tasks. I was not able to find discussions about this when wanting to use it in a function/formula.

Thank you,
Al

Spreadsheet references are relative by default.
=A1 in cell A2 refers to the cell above as you will notice when you copy cell A2 to other places.
A fully absolute reference =$A$1 actually refers to the cell A1.
=$A1 in row #2 refers to the above cell in column A.
=A$1 in column B refers to the top cell in the left neighbour column.

Villeroy,

Thank you for the quick reply. I have used relative reference in this solution and it works in most situations. However, I have run into problems with this scenario:
- in a column, for example A, I have =(An+1), where An is the cell just above the current cell... in cell A5, it would be =(A4+1)
- this is used to enumerate the rows, such as 1 - 20
- if I have the above starting at row 11, then column A for rows 11 - 30 contains the values 1 - 20
- if I delete row 15, then column A states #REF! error for all of the rows after the one that was deleted

Now, the fix is fairly simple - I reenter the formula in the first row with the #REF! error and all is good. But I was looking for a different approach so as to be able to avoid having to reenter the formula.

Thanks,
Al

E7: =OFFSET(E7;-3;-2) points to C4. E7 remains a relative reference to this formula cell.

Villeroy,

Ugh!! Sometimes the solution is so simple that one overthinks the problem.

Yep, works like a charm. All I had to do was add a test to see if the current cell is in row number 1.

Thank you for the help. Question resolved.

Al