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
[Solved] Current cell name
[Solved] Current cell name
Last edited by JustAl on Mon Aug 29, 2016 7:50 pm, edited 1 time in total.
OpenOffice 4.1.1, Windows XP and Windows 7
Re: Current cell name
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.
=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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Current cell name
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
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
OpenOffice 4.1.1, Windows XP and Windows 7
Re: Current cell name
E7: =OFFSET(E7;-3;-2) points to C4. E7 remains a relative reference to this formula cell.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Current cell name
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
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
OpenOffice 4.1.1, Windows XP and Windows 7