[Solved] Is there a way to reference "the cell above this"

Discuss the spreadsheet application

[Solved] Is there a way to reference "the cell above this"

Postby CaveDweller » Sun Jul 15, 2018 8:23 am

I just want a simple generic way to get the value of the cell above the one I'm writing the formula for. I don't want to have to know the column in advance, I just want something like what I said in the title: "get me the value of the cell above this cell".
Last edited by Hagar Delest on Sun Jul 15, 2018 9:12 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.5
Windows 10
CaveDweller
 
Posts: 2
Joined: Sun Jul 15, 2018 8:18 am

Re: Is there a way to reference "the cell above this"

Postby njhub » Sun Jul 15, 2018 8:45 am

Hello CaveDweller,

look at this file
row-1.ods
(7.94 KiB) Downloaded 222 times
LibreOffice (fr_YT): 6.1.4.2 (26/12/2018)
OS : Windows 10.0
njhub
 
Posts: 33
Joined: Thu May 17, 2018 8:36 am
Location: Mayotte YT

Re: Is there a way to reference "the cell above this"

Postby CaveDweller » Sun Jul 15, 2018 9:05 am

Thank you very much! I think I got it. At least it's working, and now I can get values from all over the place with those functions, as long as I know where they are relatively to the cell I'm writing the formula in. Thanks!
OpenOffice 4.1.5
Windows 10
CaveDweller
 
Posts: 2
Joined: Sun Jul 15, 2018 8:18 am

Re: Is there a way to reference "the cell above this"

Postby robleyd » Sun Jul 15, 2018 9:10 am

Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2858
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Is there a way to reference "the cell above thi

Postby illern » Fri Feb 15, 2019 10:59 am

This is great, but how to achieve "the cell above this but one column right"?

I'm trying to achieve a time schedule where the two columns show time of day and duration, like this:
07:20 +20 min
07:40 +30 min
08:10
and so on, but I want to be able to move the rows around freely, so they can't reference or summarize specific cells, but should rather reference and summarize the one above + the one to the right of the one above.
Open Office 4 running on Mac OS Sierra 10
illern
 
Posts: 11
Joined: Wed Jul 12, 2017 4:57 pm

Re: [Solved] Is there a way to reference "the cell above thi

Postby Lupp » Fri Feb 15, 2019 11:50 am

It's the same thing: Use relative addressing which is default anyway.

If your current cell ist D31 e.g, the cell "one row above and one column right of" is E30. Use this address to reference it. A formula containing this reference copied and then pasted elswhere or "filled" elsewhere dragging the fill handle or using the respective menu sequence will adapt automatically as you expect.

Copying the reference E30 (above example) from cell D31 to cell D32 will show it as E31. Pasting the same faormula into cell AZ111 will result in a reference to BA110, which is "one above, one right of" again.

If you want to NOT GET such an adaption you need to use ABSOLUTE addressing with the help of the $-character.

You should actually read the above linked tutorial!
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2522
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Is there a way to reference "the cell above thi

Postby illern » Fri Feb 15, 2019 12:03 pm

The "simple" referencing doesn't work with cut and paste, only with copy and paste, I found out. But since I know this now, I'll use copy paste and that'll work well. Thanks.
Open Office 4 running on Mac OS Sierra 10
illern
 
Posts: 11
Joined: Wed Jul 12, 2017 4:57 pm

Re: [Solved] Is there a way to reference "the cell above thi

Postby robleyd » Fri Feb 15, 2019 12:10 pm

You might find [Tutorial] Absolute, relative and mixed references a useful resource.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2858
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests