Ask Your Question

Calc: 'range2' always replaced by 'range1'?

asked 2018-09-16 16:51:31 +0200

knottedknickers gravatar image

updated 2018-09-17 17:37:56 +0200

I have a Calc document with multiple sheets. I want the totals of Sheet2, Sheet3, Sheet4 displayed on Sheet1. I have selected Column B on Sheet2, gone Data > Define Range, and given it a title "range1". On Sheet1 I have entered =SUM(range1) and I get the total of the range defined on Sheet2. Great! However, when I define range2 on Sheet3 and I add =SUM(range2) on Sheet1, it ALWAYS replaces range2 with range1... Is there a way around this?

edit retag flag offensive close merge delete


Btw, which LibreOffice version and OS is that? On Linux I can reproduce only in a master/6.2 debug build, not 5.4.7 nor 6.0.6 nor 6.1.0 (didn't try 6.1.1 yet).

erAck gravatar imageerAck ( 2018-09-18 12:45:19 +0200 )edit

I can reproduce it on Windows 7. Version: Build ID: 9b0d9b32d5dcda91d2f1a96dc04c645c450872bf CPU threads: 2; OS: Windows 6.1; UI render: default; Locale: en-GB (lv_LV); Calc: group

SM_Riga gravatar imageSM_Riga ( 2018-09-19 06:59:31 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-09-17 21:34:27 +0200

Seems like a bug to me. I didn't manage to find exact steps to reproduce situation when Data ranges behave in the way mentioned above, but sometimes I manage to get such a behavior from Calc no matter what ranges names are. This happens only with the secondly defined range; third, fourth and so on ranges are working just fine. Here is an example file where I managed to reproduce this error - C:\fakepath\Ranges_odd.ods Maybe somebody more experienced can explain reasons for such a behavior.

About a workaround - it seems to me that you are not working with queries from database, which this functionality is designed for. If you just want to replace range addresses in formulas (such as $Sheet.A1:A10) with textual reference, you can go to Sheet -> Named Ranges and Expressions -> Define to define referenced ranges.

If I am wrong and you need to use information from database, then you can simply go to Data -> Define Range..., delete "broken" range and redefine in once again.

edit flag offensive delete link more


There's no problem with the sample document, it seems that once saved and reloaded the problem is gone. However, I could reproduce the problem with two ranges according to the original description when creating in a new document.

erAck gravatar imageerAck ( 2018-09-18 12:03:40 +0200 )edit

answered 2018-09-17 22:18:53 +0200

erAck gravatar image

updated 2018-09-20 22:32:25 +0200

That's a bug, please submit at and report back the resulting bug number here. Thanks.

Edit: I submitted tdf#119954

Edit: fixed in master/6.2, pending review for 6-1 and 6-0

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-16 16:51:31 +0200

Seen: 60 times

Last updated: Sep 20 '18