Apache OpenOffice (AOO) Bugzilla – Issue 73766
Drag cell in formula edit mode changes address to range
Last modified: 2017-05-20 10:34:19 UTC
In cell A1, enter =$A2 Enter edit mode of cell A1 Drag cell to A3 -> Formula changes to =$A3:A3
*** Issue 75092 has been marked as a duplicate of this issue. ***
This changes an absolute reference to relative, so that replication of the new formula will introduce formula errors. This problem does not exist in 2.0.4 680m5 Build 9073. Setting keyword for regression.
Isn't this a "basic functionality that does not work"?
*** Issue 79035 has been marked as a duplicate of this issue. ***
*** Issue 79427 has been marked as a duplicate of this issue. ***
*** Issue 79175 has been marked as a duplicate of this issue. ***
*** Issue 80826 has been marked as a duplicate of this issue. ***
*** Issue 82928 has been marked as a duplicate of this issue. ***
retarget 2.x -> 3.x
As it happens so often with OOO, it seems the users will not be heard... IT IS A BASIC FUNCTIONALITY THAT DOES NOT WORK!!! I don't know how many worksheets are giving wrong results, but i bet that whoever takes a bad decision based on a formula wrecked by this problem, will never, ever, use OpenOffice again (nor will she/he speak for it). Why not be fixing the basic functionalities before of introducing new "fancy" ones???
There must be something wrong with the spreadsheet program that cannot handle dragging of cells. It's shame. The bug is disturbing users daily allthough it should be very easy to fix. I have whishfully told users that surely it will be fixed in next update. Now I just have to take claims one more year still!? I use Calc daily and this problem araises every single day. These kind of small things on top of the laggy loading and sluggish overall performance will not be tolerated long. I have decided to wait untill the version 3 and if there's no improvent still we will change to MSO. The biggest selling point being its lightning speed compared to OOo.
With OOo 2.3.1 under WinXP SP2 the behaviour is as follwed: Entering =$A2 in cell A1. Dragging the cell down. The formula changes to: =$A3, =$A4 and so on. With =A$2 and dragging down: All formulas has no change (=a$2). Dragging right, the formulas change to: =B$2, C$2 and so on. With $A$2. Dragging down or right, the formulas are always =$A$2. I can't see any bug concerning absolute references or did I understand something wrong.
May be WorksforMe since 2.3.1? I also can't reproduce that with "2.3.1 Multilingual German version WIN XP: [680m9(Build9238)]", but I was able to reproduce that for Issue 80826 (with 2.2?). Unfortunately I currently only have 2.0 and 2.3, so that I can not check whether this problem really disappeared or whether I do not remember correctly how to reproduce the bug. Can someone test this with 2.2 AND 2.3.1 to prove that the problem does no longer exist with 2.3.1?
last two reporters please reconfirm after looking at the issue Summary: "... in formula edit mode". Also the original report makes this clear. The issue is not about simply dragging a cell. It is about dragging the highlighted area when in edit mode.
Dragging the highlighted cell makes also no problem. Formula in cell A3: =$A2 (no changes) OOo 2.3.1, WinXP SP2
Ok, here a better description of the problem: Goto cell A1 Enter =$A2 Goto cell A1 again Hit F2 to enter the edit mode Drag the blue rectangle pointing to A2 to new location A3 -> Formula changes from =$A2 to =$A3:A3
I will attach a screenshot from 680_m237 Mac X11 - after dragging in edit mode. Please attach your screenshot in edit mode so we can be sure we are testing the same issue.
Created attachment 50112 [details] in edit mode, after drag
@sparcmoz: Instead of such boring "as you can read ..." comments some step by step instruction would as "dr" provided would have been helpful. I don't think that "edit mode" is an "official" term, and so these misunderstandings are inescapable. In between I also found the steps how to reproduce. Reproducible with "2.3.1 Multilingual German version WIN XP: [680m9(Build9238)]" That worked fine in "1.1.4 (German) WIN XP: [645m52 (Build 8824)]" and "2.0.2 German version WIN XP: [680m5(Build9011)]"
I do not believe that "3.0" is an adequate Target Milestone for such basic bugs. That should be fixed in the very next version.
Now confirming. But the coloured rectangle in Edit mode is only to be seen, when in Tools-Options-Calc-View 'show references coloured' (better in German: Referenzen farbig anzeigen) is activated.
It does not work with Ooo 2.3.1 It seems to me that not everyone understands what the problem is, so try this: If a1 = $b$1 + b2 Double click on it to edit formula Drag the b1 reference (drag the colored cell) to c1 and confirm edit You should have a1 = $c$1 + b2, but have a1 = $c$1:c1 + b2 Although the answer is still correct, if the cell gets copied, say to a2, it will become a2 = $c$1:c2 + b3 which is equal to c2 + b3, instead of the correct a2 = $c$1 + b3 What happens is that the absolute reference becomes a range when dragged in "editing mode". If it was not an absolute reference, there is no problem. BTY, it did work on Ooo 2.2, and it is a very useful feature. Hopefully it the OOO team would reconsider the target milestone. If Ooo is slow, we can do with it, but what is not reasonable is to have wrong results.
The problem is still present in OOo2.4 (at least on windows XP, Vista and Vista 64) Will it ever get fixed? Now is targeted for OOo 3.x (not even 3.0). It seems that votes are worth nothing. I wonder, if OOo2.2 was fine, would it be too difficult to fix? I am willing to contribute, is there anything I can do about it (I am not a programmer)? Can (and should) I pay for the fix?
*** Issue 90534 has been marked as a duplicate of this issue. ***
The problem is still present in Ooo 3.0 beta
I've got the same problems as eduardojrobledo. And I agree, though, that this problem is a kind of urgent to solve. Somehow, we want to go ahead with openoffice rather than with Microsoft. I am really looking forward to state Open Office Calc as my usual calculation sheet. Thanks so much for the project! Congratulations!
I searched for this issue, but didn't find it, and wrote another. Fortunately, someone marked it as a dupe, and pointed me here. I had written a complete description: If you edit (F2) a cell containing absolute cell references (either partially or fully absolute), and use drag & drop to move these cell references, single cell references become ranges. This causes problems if this cell is later moved. Since the end of range is relative, if you copy the cell, you will create a range where once there was a single cell. This can sometimes create an error (if the reference doesn't allow ranges), or will produce incorrect results. Example: In Cell A1, put the formula =B1+$B2+B$3+$B$4. Now edit cell A1 and use drag & drop to move the references from column B to column C. You will end up with the formula: =C1+$C2:C2+C$3:C3+$C$4:C4. This still produces the expected result, but then copy cell A1 and paste it to A2. You will get the formula: =C2+$C3:C3+$C3:C4+$C$4:C5. This will produce #VALUE because ranges aren't allowed. If you repeat the process with SUM(), the formula will provide an answer, but it probably isn't what the user would expect, as the ranges will expand when the cell is copied/pasted. I agree, this bug is nasty, and if I can get a development system set up, I will work on fixing it. Based on my testing, it looks pretty simple. When you MOVE a cell reference, something is flagging the END OF RANGE as relative. If you take a relative range (let's say =SUM(A1:A2) and edit it, and SHRINK the range back to one cell, it doesn't produce SUM(A1:A1), it produces SUM(A1). However, if you try to shrink a "mixed absolute" range, it will never produce a single cell. In order to shrink to a single cell, the absolute-ness of both the start and end must agree. So, $A1:$A2 will shrink, but $A1:A$2 won't, because the two terms don't agree in absolute-ness. That's where I think the problem lies - moving a single cell seems to get flagged as being "relative" for the ending term (when you STRETCH a single absolute cell, it will create a range, the ending term is relative). Excel (which doesn't have this bug), makes sure when it creates a range from a single cell, the absolute-ness of the ending term MATCHES the absoluteness of the existing term. If Calc did this, I believe the bug would go away. Now, who can find that part of the code before I do?
It's still there in 3.0 final... phuuh it's such a basic feature in spreadsheet editing that I'm astonished that it doesn't get more attention from users. How can it be that broken basic functionality has been dragged along since version 2.1?
I can reproduce duhhh's problem as well as the one mentioned by the original poster. Please care abut this issues.
This bug has more than a Year!!! I am writing this in an effort to help developers better understand of how important this is. «Background» Edit the contents of a cell is the most basic and, by large, the most common task any user performs in a worksheet. Anything that needs to be changed is done by a "editing a cell". Many Calc users just look at the graphs, or read the results of a worksheet, but any "power user" would do some sort of change in the worksheet. For those users, any mistake on the edit process could have far reaching effects. «The problem» This specific problem is related to how a cell reference is interpreted by the application. It could be said that a direct cell reference (i.e. D6), an absolute cell reference (i.e. $D$6) and a range of "one cell" (i.e. D6:D6 direct or absolute) point to the same cell. And any of those references would give the same result when used in a cell. While yes, that's correct, this is not what this Issue is really about. The problem becomes a real issue when those references are copied to other cells. That's a common procedure to make formulas in several cells do the right thing. Edit once, copy many, is what I like to call this concept. «An example of the problem» 1.- Open the attached File. On cells C10:D19 there is an empty RED space to work the example. There are some more cells bellow to help understand what happens. 2.- Fill down (copy cells C10:D10 down) up to cells C19:D19. There will be a mistake on cells D13, D16 and D19. The correct result should be what is in cells C23:D32. 3.- Edit cell D10. The reference to AE5 is incorrect. While editing cell D10 move the reference (with the mouse) to cell AE2. The reference should change to $AE$2:AE2. 4.- Fill down C10:D10. A different error is in cells D13, D16, and D19. e.g. The referenced cell $AE$2:AE2 changes to $AE$2:AE5 at cell D13. That breaks the "edit once, copy many" sequence!!!!!!!!!! The cell D10 needs to be re-edited to erase the incorrectly added part AE2. The correct formula has been set up in C23:D23 for you to try how it should work correctly. Any user that depends on copying formulas to several cells would have problems with this bug. Any power user would be bitten by this bug. Hope this helps.
Created attachment 61651 [details] A long example on this Issue
Thank you Isaac Marcos for your very clear example. However, I do not get exactly the results that you explain in your note. When I edit cell D10, using key F2, and I move reference $AE$5 up to cell AE2, then I get $AE$2:AE2. However, and I find it a very severy error, when I confirm the formula with the Enter key, the reference is changed AUTOMATICALLY, WITHOUT WARNING NOR LOGIC to $AE$2:$AE$2. The problem still persists, but it is of another nature. I am using Ver 3.0.0 OOO300m9 Build 9358.
> Thank you Isaac Marcos for your very clear example. YW. > When I edit cell D10, using key F2, and I move reference $AE$5 up to cell AE2, > then I get $AE$2:AE2. Same here, that's the issue a several people have voted for!! > However, and I find it a very severy error, when I confirm the formula with > the Enter key, the reference is changed AUTOMATICALLY, WITHOUT WARNING NOR > LOGIC to $AE$2:$AE$2. If that hapens to you, that's a different item, But: Why the value is changed to a RANGE in the first place. That is the root of the problem.... > The problem still persists, but it is of another nature. JMO, is the same base problem, a reference changed to a RANGE with an additional twist in your case. > I am using Ver 3.0.0 OOO300m9 Build 9358. Sorry, I should have reported my version: Ver 3.0.1 000300m15 9379 (Debian OS). But as it has been reported by so many people for more than TWO years, that it should be quite irrelevant. It has been happening for a long time, and still does.
There are several screnanios where this bug breaks the formula or returns just plain wrong results... very simple way to reproduce: input "1" to [A1] input "2" to [A2] input "=A$1" to [A4] enter edit mode of [A4] and drag the reference (blue square) with mouse from [A1] to [A2] copy the [A4] to [A5]...[A10] See reference of [A5]...[A10], they are completely screwed. Think of what can happen on more complicated sheets with more complicated functions...
mlaakso, Folowing your example, when I drag the formula in A4 from A1 to A2 I get "=A$2:A2" while in edit mode, and "=A$2:A$2" after pressing the Enter key, which returns the value "2". When I copy down A4 to A5...A10, since my formula is 100% absolute "=A$2:A$2" I get the value "2" all the time. However, I'm afraid that's not what mlaakso obtains since he says everything is screwed up. My conclusion is that somewhere there has been a change betweeen builds, and the behaviour has changed. But the bug is not fixed. I am using Ver 3.0.0 OOO300m9 Build 9358.
There are two different bugs although related: FIRS BUG: Absolute reference to a cell turning into a reference to a range. Try this: B1 = $A$1 + A2, then, if to copy B1 to B2, B2 = $A$1 + A3, the correct beheavior. If you want B2 = $A$2 + A3 you can either edit the formula by typing or while editing it, drag the colored A1 cell to A2. But, and this is the bug, OOo changes it to the range $A$2:A2, then B2 = $A$2:A2 + A3. There is noting wrong with the result of B2 as the range starts and ends in the same cell, but if it gets copied, say to B3, the formula will no be as intended (B3 = $A$2 + A4) but B3 = $A$2:A3 + A4, which by way of how OOo handles ranges in a formula is equivalent to B3 = A3 + A4. SECOND BUG: Absolute reference to a cell turning into a reference to an absolute range. The process is the same as the first bug, but in some cases that I can't replicate every time, the range reference is absolute in the starting and ending cells. I am using OOo 3.0.1 in windows. Will it ever get solved? It is the 4th most voted defect in the spreadsheet component and 37th overall. I am tired of this and this is my last comment on this bug. We users keep talking about it. Every one can replicate de basic form of the bug. It gives WRONG results. It is almost impossible to verify in a large and complex spreadsheet. IT WAS NOT PRESENT in older versions of OOo. The OOo team keeps changing the target milestone and now it is not even a specific one. It is not assigned to anyone. Developers DO NOT pay attention to users no matter the 17 votes it has. It seems to me that OpenOffice will never be a trusty application. It is more of a showcase for opensource advocates (SUN and IBM included) that need to prove that a good and large application can be developed?
vsoler, In windows the behaviour is different and I believe it has been consistent since version 2.1. My current version is OOO310m9 (Build:9396) When I drag the reference from [A1] to [A2] in edit mode the reference changes to "A$2:A2" and doesn't change anyhow after pressing the enter. When you copy the [A4] to [A5]...[A10] the reference changes to following: A5: "A$2:A3" A6: "A$2:A4" A7: "A$2:A5" A8: "A$2:A6" A9: "A$2:A7" A10: "A$2:A8"
mlaakso, In my system, Ver 3.0.0 OOO300m9 Build 9358 on Windows XP home edition Ver 5.1, the references change when I press the Enter key. Is it beacause I am using Windows and perhaps you are using Linux? I am not able to give an answer. Vicente Soler
Please, we should focus on the main problem.!!! The basic problem is a process, component, that changes a simple reference to a range reference. As a wild guess, perhaps someone tried to solve the issue working on the way absolute references are managed, and that activates sometimes or not. But the real problem here to solve is why when the colored cell is moved, it changes the cell reference to a RANGE. If that ERROR were to be solved, the other problems would simple not happen. Anyone knows: Who is responsible to work on this bug?
Trying to reduce the test case, I found some additional information: (1) In cell A1, enter =$A2:$A2, enter edit mode, drag highlight to cell A3, formula changes to =$A3. In other words, the range is changed to an address, but the edit yields a correct formula. (2) In the various examples, in edit mode use point-and-click instead of drag. An additional address is inserted but colon (:) is absent and the formula is reported as an error. (3) Start with a relative formula - in cell A1 enter =A2. Then edit->drag works correctly but edit->point and click gives the error from (2) It appears there is a more general problem with cell edits, this issue is only one example.
point-and-click should not mixed with this case. There's no inconsistency how it works. Anyway, point-and-click shouldn't add any colon to any formula, semicolon at most if anything. But that's another case, please open another issue if you'd like to have enhancement to point-and-click in formula edit mode.
@ sparcmoz point (1) and (3) (only drag part) confirmed here, same behavior. points (2) and (3) (point and click) should be dealt with in a different report, IMO. @ mlaakso I agree with your description. The initial problem description reads "Drag cell in formula edit mode changes address to range", I understand that "drag" to mean moving the colored cell with the mouse, and the problem to be related to a change to RANGE. Is there a different description we should be using?
*** Issue 109757 has been marked as a duplicate of this issue. ***
Fixed in CWS "dr73".
reassigning to QA for verification
verified in internal build cws_dr73
<Sigh> Still alive and kicking. Ooo 3.2.1 OOO320m18 (Build 9502) on Win XP. 1. In cell A1, enter "= $B1" 2. Double click cell A1. Drag the highlighted box down to B2. Cell A1 formula changes to "=$B2:B2". 3. Copy formula down to cell A2. Formula in A2 now reads "=$B3:B3" Also, 1. In cell A3, enter "= $B$3" 2. Double click cell A3. Drag the highlighted box down to B4. Cell A3 formula changes to "=$B$4:B4". 3. Copy formula down to cell A5. Formula in A5 now reads "=$B$4:B5"
Someone please help! I don't have the necessary skills to contribute, but this is a serious deficiency which needs to be plugged. Thanks in advance.
abhio, this was fixed for 3.3. You can download a release candidate version at http://download.openoffice.org/all_rc.html.