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

Discuss the spreadsheet application
Post Reply
CaveDweller
Posts: 7
Joined: Sun Jul 15, 2018 8:18 am

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

Post by CaveDweller »

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
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"

Post by njhub »

Hello CaveDweller,

look at this file
row-1.ods
(7.94 KiB) Downloaded 1557 times
LibreOffice (fr_YT): 6.1.4.2 (26/12/2018)
OS : Windows 10.0
CaveDweller
Posts: 7
Joined: Sun Jul 15, 2018 8:18 am

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

Post by CaveDweller »

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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

You may find [Tutorial] Absolute, relative and mixed references helpful.
If you are new to spreadsheets, you may also find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
illern
Posts: 11
Joined: Wed Jul 12, 2017 4:57 pm

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

Post by illern »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
illern
Posts: 11
Joined: Wed Jul 12, 2017 4:57 pm

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

Post by illern »

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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

You might find [Tutorial] Absolute, relative and mixed references a useful resource.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply