Odd behaviour with cell references when inserting new columns

English
, , , ,
#1

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 4.2.3.3.

#4

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

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.

#5

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

#6

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.

#7

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

#2

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.

#3

I can’t reproduce with 4.2.3.3 Win7x64

#8

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.