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

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

knottedknickers
15 ●7 ●10 ●15

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

Comments

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 ( 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

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

add a comment

0

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

SM_Riga

932 ●4 ●7 ●24 https://ask.libreoffic...

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

Comments

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 ( 2018-09-18 12:03:40 +0200 )edit

add a comment

0

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