Issue 73766 - Drag cell in formula edit mode changes address to range
Summary: Drag cell in formula edit mode changes address to range
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 2.1
Hardware: All All
: P3 Trivial with 21 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: oooqa, regression
: 75092 79035 79175 79427 80826 82928 90534 109757 (view as issue list)
Depends on:
Blocks: 90135
  Show dependency tree
Reported: 2007-01-22 20:20 UTC by daniel.rentz
Modified: 2017-05-20 10:34 UTC (History)
9 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

in edit mode, after drag (52.63 KB, image/png)
2007-12-05 11:40 UTC, sparcmoz
no flags Details
A long example on this Issue (19.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-04-17 20:14 UTC, isaacmarcos
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description daniel.rentz 2007-01-22 20:20:33 UTC
In cell A1, enter =$A2
Enter edit mode of cell A1
Drag cell to A3
-> Formula changes to =$A3:A3
Comment 1 ooo 2007-03-05 11:38:27 UTC
*** Issue 75092 has been marked as a duplicate of this issue. ***
Comment 2 sparcmoz 2007-03-05 21:07:44 UTC
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.
Comment 3 pavel 2007-03-06 13:33:56 UTC
Isn't this a "basic functionality that does not work"?
Comment 4 ooo 2007-07-09 11:19:59 UTC
*** Issue 79035 has been marked as a duplicate of this issue. ***
Comment 5 frank 2007-07-10 09:24:16 UTC
*** Issue 79427 has been marked as a duplicate of this issue. ***
Comment 6 gercokees 2007-07-18 19:48:25 UTC
*** Issue 79175 has been marked as a duplicate of this issue. ***
Comment 7 Rainer Bielefeld 2007-08-20 06:27:45 UTC
*** Issue 80826 has been marked as a duplicate of this issue. ***
Comment 8 frank 2007-10-24 13:34:30 UTC
*** Issue 82928 has been marked as a duplicate of this issue. ***
Comment 9 niklas.nebel 2007-12-04 18:07:21 UTC
retarget 2.x -> 3.x
Comment 10 eduardojrobledo 2007-12-04 23:30:31 UTC
As it happens so often with OOO, it seems the users will not be heard...


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"
Comment 11 mlaakso 2007-12-05 00:58:24 UTC
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. 

Comment 12 jolatt 2007-12-05 07:35:01 UTC
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
Comment 13 Rainer Bielefeld 2007-12-05 09:52:40 UTC
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?
Comment 14 sparcmoz 2007-12-05 10:06:05 UTC
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.
Comment 15 jolatt 2007-12-05 11:16:13 UTC
Dragging the highlighted cell makes also no problem.
Formula in cell A3: =$A2 (no changes)

OOo 2.3.1, WinXP SP2
Comment 16 daniel.rentz 2007-12-05 11:35:50 UTC
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
Comment 17 sparcmoz 2007-12-05 11:39:49 UTC
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.
Comment 18 sparcmoz 2007-12-05 11:40:58 UTC
Created attachment 50112 [details]
in edit mode, after drag
Comment 19 Rainer Bielefeld 2007-12-05 12:04:30 UTC
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)]" 

Comment 20 Rainer Bielefeld 2007-12-05 12:09:59 UTC
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.
Comment 21 jolatt 2007-12-05 15:54:35 UTC
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.

Comment 22 eduardojrobledo 2007-12-06 16:12:07 UTC
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.
Comment 23 eduardojrobledo 2008-03-29 15:06:14 UTC
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?
Comment 24 frank 2008-06-09 19:28:47 UTC
*** Issue 90534 has been marked as a duplicate of this issue. ***
Comment 25 eduardojrobledo 2008-06-09 21:52:39 UTC
The problem is still present in Ooo 3.0 beta
Comment 26 natx 2008-06-10 14:09:40 UTC
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!
Comment 27 duhhh 2008-06-10 17:57:06 UTC
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

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?
Comment 28 mlaakso 2008-11-11 23:14:03 UTC
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?
Comment 29 pfeffer2de 2009-01-09 13:49:08 UTC
I can reproduce duhhh's problem as well as the one mentioned by the original 
Please care abut this issues.
Comment 30 isaacmarcos 2009-04-17 20:13:11 UTC
This bug has more than a Year!!!

I am writing this in an effort to help developers better understand of how
important this is.


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

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.
Comment 31 isaacmarcos 2009-04-17 20:14:51 UTC
Created attachment 61651 [details]
A long example on this Issue
Comment 32 vsoler 2009-04-18 06:44:50 UTC
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

The problem still persists, but it is of another nature.

I am using Ver 3.0.0 OOO300m9 Build 9358.
Comment 33 isaacmarcos 2009-04-18 11:21:59 UTC
> Thank you Isaac Marcos for your very clear example.

> 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.
Comment 34 mlaakso 2009-04-19 04:05:02 UTC
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... 

Comment 35 vsoler 2009-04-19 08:23:19 UTC

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.
Comment 36 eduardojrobledo 2009-04-19 13:50:08 UTC
There are two different bugs although related:

FIRS BUG: Absolute reference to a cell turning into a reference to a range. Try
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
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

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?

Comment 37 mlaakso 2009-04-19 14:26:50 UTC

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"
Comment 38 vsoler 2009-04-19 16:38:44 UTC

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
Comment 39 isaacmarcos 2009-04-19 20:37:05 UTC
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?
Comment 40 sparcmoz 2009-04-19 23:38:57 UTC
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. 
Comment 41 mlaakso 2009-04-20 00:01:02 UTC
point-and-click should not mixed with this case. There's no inconsistency how it

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. 
Comment 42 isaacmarcos 2009-04-20 00:47:16 UTC
@ 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,

@ 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?
Comment 43 Regina Henschel 2010-03-03 09:30:51 UTC
*** Issue 109757 has been marked as a duplicate of this issue. ***
Comment 44 niklas.nebel 2010-03-03 12:44:46 UTC
Fixed in CWS "dr73".
Comment 45 niklas.nebel 2010-03-17 17:22:26 UTC
reassigning to QA for verification
Comment 46 oc 2010-04-09 10:20:15 UTC
verified in internal build cws_dr73
Comment 47 abhio 2010-09-02 14:15:43 UTC

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"

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"

Comment 48 abhio 2010-11-03 08:47:13 UTC
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.
Comment 49 niklas.nebel 2010-11-03 10:41:00 UTC
abhio, this was fixed for 3.3. You can download a release candidate version at