Calc - Relative reference in external data txt file

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:


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, this works!

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


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.

1 Like

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