Ask Your Question

How to prevent formulea updating if rows are Inserted / Deleted?

asked 2015-04-29 15:50:13 +0200

miclac gravatar image

updated 2015-08-27 23:15:17 +0200

Alex Kemp gravatar image

Hi, I'm using Calc (Version: 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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-04-29 17:21:52 +0200

miclac gravatar image

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

edit flag offensive delete link more

answered 2015-04-29 16:21:19 +0200

LibreGuy gravatar image

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"

edit flag offensive delete link more


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.

miclac gravatar imagemiclac ( 2015-04-29 17:17:07 +0200 )edit

answered 2015-04-29 22:33:40 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-29 15:50:13 +0200

Seen: 104 times

Last updated: Apr 29 '15