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