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

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

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

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

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

Sort by » oldest newest most voted

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.

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.

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

That's a bug, please submit at https://bugs.documentfoundation.org/ 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

more