| Summary: | Sort changes relative references same as absolute ones | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Claudio <claudiofarinati> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | UNCONFIRMED --- | ||
| Severity: | normal | CC: | himajin100000, miguelangelrv, saravananu4420, yallabalagan |
| Priority: | medium | ||
| Version: | 7.1.0.3 release | ||
| Hardware: | All | ||
| OS: | Windows (All) | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Attachments: |
Sample sheet
Sample relative/absolute named ranges i got output ,form the bug |
||
|
Description
Claudio
2021-02-05 11:16:33 UTC
Maybe something to do with Menu/Tools/Options/LibreOffice calc/Calculate - Update references when sorting range of cells. (In reply to m.a.riosv from comment #1) > Maybe something to do with > Menu/Tools/Options/LibreOffice calc/Calculate - Update references when > sorting range of cells. In "Menu/Tools/Options/LibreOffice calc/Calculate" I don't see anything about "update references..." or similar. [Automated Action] NeedInfo-To-Unconfirmed It looks like the "Update references when sorting range of cells" option is under Menu/Tools/Options/LibreOffice calc/General in v7.1.1.2. Toggling that box does appear to affect the outcome. I'll let you decide if it helps your problem or not. (In reply to Michael from comment #4) > It looks like the "Update references when sorting range of cells" option is > under Menu/Tools/Options/LibreOffice calc/General in v7.1.1.2. > > Toggling that box does appear to affect the outcome. I'll let you decide if > it helps your problem or not. Right, it was my mistake. Thanks a lot Claudio Confirmed on 7.1.2.2 Kubuntu 64bit PC. The problem is *much* worse: multiple reproducible bugs around relative (and absolute) name ranges, rendering them untrustworthy of calculations. multiple examples screencast / videos of these bugs: https://youtu.be/R0y8Zg_d-vs https://youtu.be/TOphlzoWfvE bug1: 1. create a absolute named range 2. convert it to relative 3. result - formulas (sometimes) give error 522 (circular reference) bug 2: 1. create a relative named range 2. go to another cell 3. press Ctrl+F3 4. result: The named range shows another address! bug 3 1. create relative named range 2. create a formula referring to that named range (e.g. "=named+4" ) 2. sort 3. named range points to another address; formula gives a wrong result bug 4: 1. change named range from relative/absolute, to half-relative ($B$6 -> B$6 , $B6). 2. exactly same address, different results or error messages wait, there's more but difficult to reproduce: sometimes relative named range error (522) magically fixed after changing values in the sheet. or the error is removed, but showing abnormal values. Created attachment 171243 [details]
Sample relative/absolute named ranges
How relative and absolute 'named ranges' work it's a bit tricky.
When a relative range is created, it is relative to the cell where you are at the moment of creation, if you go to another cell then it changes with rows and columns relatives to the change of position.
Enter =cell() in A1 and A2
e.e go to C2, [Ctrl+F3], create 'test' 'sheet1.A1'
In C2: =test -> result is 'A1'
In C3: =test -> result is 'A2'
Copy C2 to C3: result in C3 is 'A2'
Such kind of behavior is very useful to solve some situations, but no easy to understand with two cells with a formula with the same 'named range' give different results.
So messing that with sort adapting references looks to me not easy to understand the result.
Created attachment 185039 [details]
i got output ,form the bug
Exactly as old Bug 81633. Not present in v.6, present in v.7. (1) open the attachment (2) select the rows from 2 to 6 (3) sort ascending in column B (date) The column E (each line with a relative reference to preceding line) is changed in the same way of column F (each line with an absolute reference to preceding column). software:linux 5.15 status : output produce ... (In reply to Saravanan U from comment #9) > Exactly as old Bug 81633. Not present in v.6, present in v.7. > ... > status : output produce ... Have you read comment#1 |