Page 1 of 1

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

PostPosted: Sun Jul 15, 2018 8:23 am
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".

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

PostPosted: Sun Jul 15, 2018 8:45 am
by njhub
Hello CaveDweller,

look at this file
row-1.ods
(7.94 KiB) Downloaded 272 times

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

PostPosted: Sun Jul 15, 2018 9:05 am
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!

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

PostPosted: Sun Jul 15, 2018 9:10 am
by robleyd

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

PostPosted: Fri Feb 15, 2019 10:59 am
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.

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

PostPosted: Fri Feb 15, 2019 11:50 am
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!

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

PostPosted: Fri Feb 15, 2019 12:03 pm
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.

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

PostPosted: Fri Feb 15, 2019 12:10 pm
by robleyd
You might find [Tutorial] Absolute, relative and mixed references a useful resource.