[Solved] Current cell name

Discuss the spreadsheet application

[Solved] Current cell name

Postby JustAl » Sun Aug 28, 2016 9:52 am

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
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
JustAl
 
Posts: 4
Joined: Thu Aug 20, 2015 2:23 am

Re: Current cell name

Postby Villeroy » Sun Aug 28, 2016 10:09 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26639
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Current cell name

Postby JustAl » Sun Aug 28, 2016 10:48 am

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
OpenOffice 4.1.1, Windows XP and Windows 7
JustAl
 
Posts: 4
Joined: Thu Aug 20, 2015 2:23 am

Re: Current cell name

Postby Villeroy » Sun Aug 28, 2016 10:59 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26639
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Current cell name

Postby JustAl » Mon Aug 29, 2016 7:48 pm

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
OpenOffice 4.1.1, Windows XP and Windows 7
JustAl
 
Posts: 4
Joined: Thu Aug 20, 2015 2:23 am


Return to Calc

Who is online

Users browsing this forum: Villeroy and 12 guests