Ask Your Question

Odd behaviour with cell references when inserting new columns [closed]

asked 2014-05-03 05:26:21 +0100

kiwi_writer gravatar image

updated 2015-09-07 00:38:59 +0100

Alex Kemp gravatar image

I have a col of numbers (say in B1 to B6), which is summed at the bottom - B7 is SUM(B1:B6). Cells in formula are relative. I insert two cols to left of B. I would expect the formula to change to SUM(D1:D6) - but it doesn't. It becomes SUM(B1:D6)

If I make the cell refs absolute - SUM($B$1:$B$6) and insert the new cols, I get SUM($B$1:$D$6).

This is contrary to the guidance given in the Help file (absolute addresses in spreadsheets) - 'After having inserted a new column A, the formula =SUM(A1:B2) will be automatically updated to =SUM(B1:C2).'

This behavior occurs in both column insert and cell insert.

Has anyone experienced this? Is there a solution, or should I file a Bug. Has only been a problem since

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 20:03:54.609456

2 Answers

Sort by » oldest newest most voted

answered 2014-05-04 02:16:41 +0100

kiwi_writer gravatar image

Thanks for the suggestions. I have replicated the issue on another machine running Win7 64, and with a completely clean install of on a system running XP that has never had LO loaded. I also replicated the problem on a reversion to

The key is that the column containing the data must be included in the selection - that is, to insert two cols to left of col B, I am selecting A and B then going Insert Left. If I just select A and insert a single col, the data moves to col C and the formula correctly transforms to SUM(C1:C6). I've tried this with data in col G and cols inserted left (including G), with the same outcome.

I'll file a Bug report.

edit flag offensive delete link more



I can see the issue now, Thanks for your reported bug, marked as duplicate of the reopened fdo#77647

m.a.riosv gravatar imagem.a.riosv ( 2014-05-04 03:53:27 +0100 )edit

v4.2.2.3 remains unaffected. "The key is that the column containing the data must be included in the selection - that is, to insert two cols to left of col B". It is more complex than this. Here is a test using v4.2.3.3: A1 contains 1, A2 contains 2, A3 contains =SUM(A1:A2). Highlight column A, insert a new column. B3 has expected value. Highlight columns A and B and insert two columns. D3 displays the error. Undo this last action. Highlight columns B and C and insert two columns. D3 has expected value.

oweng gravatar imageoweng ( 2014-05-04 11:16:16 +0100 )edit

A bit of light, with the option "Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted" is disable it works properly fdo#77647 comment 23

m.a.riosv gravatar imagem.a.riosv ( 2014-05-04 19:03:26 +0100 )edit

answered 2014-05-03 08:06:20 +0100

oweng gravatar image

It appears that release v4.2.3 has introduced several problems (particularly in Calc) that were not there in v4.2.2. I cannot reproduce the described issue using v4.2.2.3, so I would suggest reverting to an earlier version as a workaround. Perhaps also try resetting the user profile.

edit flag offensive delete link more


I can't reproduce with Win7x64

m.a.riosv gravatar imagem.a.riosv ( 2014-05-03 12:26:53 +0100 )edit

Question Tools

1 follower


Asked: 2014-05-03 05:26:21 +0100

Seen: 537 times

Last updated: May 03 '14