Calc - Relative reference in external data txt file

[lovecraft22]

All,
I have a script which assembles a txt file with some data. This is then appended to a Calc sheet. Most of the data will be raw numbers but from time to time I need to include some simple formula which will also be pasted into Calc and will need to work. It would be something like this:

2
4
7
=previous_cell/2
1
5

This would be easy to do if I only knew what would be the location of the cell containing “7” once the data is pasted to Calc… which I don’t… Is there a way to include a formula which does not use any address (e.g =A3/2) but rather a relative address from the current cell (e.g =1_cell_above/2)?

Thanks!

[anon73440385]

Hello,

use

=OFFSET(INDIRECT(CELL("address"));-1;0;1;1)/2

[lovecraft22]

@anon73440385

=OFFSET(INDIRECT(CELL(“address”));-1;0;1;1)/2

Thanks, this works!

[erAck]

Another solution than OFFSET() with less typing and calculation overhead is to switch the document to Excel R1C1 formula syntax in Tools → Options → Calc → Formula, Formula Options and in the to be pasted data use

=R[-1]C/2

where R[-1]C means “one row above same column”. You can switch the formula syntax back and forth between this and Calc A1 to see the same reference in different notations.

[lovecraft22]

Thanks, this is another cool thing I didn’t know about! Note that on MacOS the option is in LibreOffice → Preferences → LibreOffice Calc → Formula