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

[Hagar Delest]

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

[njhub]

Hello CaveDweller,

look at this file

row-1.ods

(7.94 KiB) Downloaded 464 times

[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!

[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

[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.

[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!

[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.

[robleyd]

You might find [Tutorial] Absolute, relative and mixed references a useful resource.