Hi,
I’m using Calc (Version: 4.1.6.2) to generate a csv. To make use of formatting, data rages, and other Libre office features the file is in ods format with two sheets, the second sheet refers to data in the first and places text delimiters where needed, and is used to ‘save as’ in the required csv format. The issue I have run into is that when I insert or remove rows in sheet1 the relative addressing used in the formulae in sheet2 updates.
I had thought I could try absolute addressing in the second sheet formulae but I already employ this with select parts of the formulae to allow for expansion in the number of rows and columns.
I’ve been managing by selecting the top row in sheet2 and scrolling to the required length to renew the contents before exporting my file, but as I have 34 of these files to edit and some of them have as many as 12,000 rows, this scrolling is time consuming.
Thanks for reading.
Michael
Not sure if this does what you need since I’m quite new to LibreOffice.
Go to Tools->Options->LibreOffice Calc->General
Deselect the option “Expand references when new colums/rows are inserted”
Hi LibreGuy, Thanks for the answer. Unfortunately it did not work; the option was already deselected, so I tried it both ways and the result is the same.
I’ve had an idea for a work around by generating my formulae to recognise blank rows and populate cells with “”, then grow my csv table of formulae to larger than needed, and set all referencing to absolute for every row except the top (in case I need to expand it further later).
Thanks
If second sheet it’s only for text delimiters, I think this can be done when saving as csv, selecting edit filter settings.
An option is using OFFSET(), putting in Sheet2.A1 =OFFSET(Sheet1.$A$1;ROW()-1;COLUMN()-1), always reference the same cell in sheet1 as the formula is.