Apache OpenOffice (AOO) Bugzilla – Issue 7500
Copying across fromulas from one merged cell to another.
Last modified: 2013-08-07 15:14:30 UTC
I have merged three cells into one in a number of places. I have a formula in the first merged cell that I want to copy along to the others. 1) I can't copy and paste as it comes up with, "Cell merge not possible if cells already merged!" 2) I want it to update the formula as it does from one normal cell to another, ie. =VLOOKUP(I14;$Jobs.$A$1:$D$125;2) in cell A1 becomes =VLOOKUP(I15;$Jobs.$A$1:$D$125;2) in cell A2. However, when I drag the bottom right as you normally would it re- splits my cell into it's original 3 cells with the formula in each cell. Help!!
Stephen, thanks for taking the time to post this issue. First of all, requests for support are best directed to the users mailing list. IssueZilla is reserved for bugs or enhancement request for OO. Okay, here's my responses to your two issues: 1. When you copy a merged cell, OO copies the merge cell format as well. In short, just copy and paste to a regular cell and OO will handle the merging for you. For example, A1 and A2 are merged and contains the formula =SUM(C1;D1) Copy the merged cell, and paste into A3. No need for you to merge the cells manually. 2. I think you are referring to OO changing the values in the function relative to the new location. Using the example above, the formula in A3 will be =SUM(C3;D3) The way to work around this is to cut the contents of A1 and paste it back into A1 and A3 in order to preserve your formula.
Hi Bettina, the handling of merged cells should be improved: it should be possible to paste special with format unchecked with merged cells.
*** Issue 8301 has been marked as a duplicate of this issue. ***
Paste in any form will not work when copying and pasting from one set of merged cells to an identical set of merged cells. To reproduce: Merge cells A1 and B1. Merge cells A2 and B2. Enter a formula into the merged A1:B1 cell. Select the merged cells and Ctrl-C (copy). Move cursor to the merged A2:B2 cell. Paste (with Ctrl-P or cursor selection). You get "Cannot copy to merged cells." This happens in 1.1RC1.
Summary: Copying across formulas from one merged cell to another.
*** Issue 46166 has been marked as a duplicate of this issue. ***
*** Issue 61598 has been marked as a duplicate of this issue. ***
This error is also generated when you try to use the Format Paintbrush from one merged cell to another merged cell.
Pls. mention interesting comments in Issue 68784!
*** Issue 68784 has been marked as a duplicate of this issue. ***
found the same issue still on OOo 2.0.3: Calc: merge cells A1:A2 merge cells B1:B2 enter text "any text" in A1:A2 copy A1:A2 paste to B1:B2 Error message: "Zusammenfassen nicht verschachteln!" (german version). This is VERY inconvinient!
I found the same issue in StarOffice 8.0 Product Patch 5. This issue was not found in StarOffice 8.0 to StarOffice 8.0 Product Patch 4. Calc: merge cells A1:A2 merge cells B1:B2 enter text "any text" in A1:A2 copy A1:A2 paste to B1:B2 Error message: "Zusammenfassen nicht verschachteln!" (german version). This is very inconvenient! Is that a bug or has been this feature removed?
Created attachment 42843 [details] Clean Fix to copy-paste problem on merged cells
Hello, Here is a rather simple fix to this problem. And it works well. I have observed that copying merged cells works on unmerged cells. So I unmerge it before the copy. It works very well, and I don't see any side effects. It was what I was doing manually anyway, when I didn't know how to hack in OOo ;).
Hello, Here is a rather simple fix to this problem. And it works well. I have observed that copying merged cells works on unmerged cells. So I unmerge it before the copy. It works very well, and I don't see any side effects. It was what I was doing manually anyway, when I didn't know how to hack in OOo ;). Can anyone confirm me if it's ok and when will it be included ? Thanks,
Hello, Here is a rather simple fix to this problem. And it works well. I have observed that copying merged cells works on unmerged cells. So I unmerge it before the copy. It works very well, and I don't see any side effects. It was what I was doing manually anyway, when I didn't know how to hack in OOo ;). Can anyone confirm me if it's ok ? Thanks,
Created attachment 42870 [details] a better version, which unmerges the real dest range
adding me to cc - Sophie
Hello, I'm using OOo since June 2006 and find very much inconvenient and handicaping not to be able to insert a row or colomn when cells are merged. I got OOo 2.3.0 for testing and this lack of flexibility is still there. May be this will be fixed since, as for me, it can be a matter to go back to ms excel. very bad. Please can this feature be fixed in the next version of OOo Thanks
Hi Niklas, please have a look at this one as it is a PATCH now. Frank
This serious problem exists for more than 5 years!!!! I'm shocked. :(
cl->nn: Can you document the reason why this patch is not applied or apply it?
The patch doesn't add undo for unmerging, and it can lead to invalid attribute state: Merge B1:B3, merge C1:D1, copy C1, paste in A2.
->nn : I am not sure I get your point. You just have to change second parameter to from "FALSE" to "TRUE" in this 2 lines patch, in order to have a ScUndoUnmerge done by UnmergeCells. And, for your use case, the result seems graphically coherent with what the users tried to do. Maybe I can force the paste share the same start point that the copy, but I do not see why. Can you light me a little, please ? Thanks,
A cell can only be part of one set of merged cells. To allow "layered" merging, more has to be changed (repaint, for example), and even then, it would probably me more confusing than helpful.
Created attachment 49194 [details] A patch improved according to Niklas comments
mloiseleur->nn: I come with this idea, since OpenOffice is not ready yet to play with overlapping range : I unmerge all of them which are in the way. What do you think ? Is this new patch ok to get into mainline ? Regards,
Not finished in time for 2.4, retargeting.
Nested loops over individual columns/rows should be avoided. It's a performance problem already for large selections, and will cause more problems if the maximum column/row count is increased. Otherwise, this seems to work and I'll try to get it into 3.1 (too late for 3.0).
Created attachment 54755 [details] New version of the patch, more faster and robust
->nn: Then you can base your work on the last version of the patch. It fix problems on infinite loop, but can still be improved on performance, if you manage to remove call to MergeCells/UnmergeCells. The last problem on this patch is on a block insertion, it can screw up totally the sheet. Hope this helps you, Regards,
Over a year since a patch was produced, and this still hasn't been fixed? I run into this bug daily, since I started using OO. The "other" sheet doesn't have this problem, since I'm very used to copy and paste merged cells. I suppose you could say I make horrible spreadsheets, but I commonly use small blocks and merge cells to make larger blocks. So pretty much every sheet I have, I see this error whenever I try to copy a cell. I must first select the exact number of cells in the target area, then cut the target area, then select the source area, then copy, then select the top left cell and paste. This is a serious drain on workflow, and when I forget to follow the procedure, it is also a waste of time to start all over. One question I have about the proposed patch. If you drag the cells to make duplicates, does it handle making the duplicates merged cells as well? Meaning if I have A1:A2 merged, and I grab the lower right corner of A2 and drag it down 3 cells, do I end up with the only expected behaviour? Do I end up with B1:B2, C1:C2, and D1:D2 merged and copies of the formula from A1:A2 present?
>mloiseleur: the last pacth is about inserting merged cell's patch,it is wrong.the merged cell's issue is issue 8302.
maoyg: oops, you're right. Sorry for the mistake.
>mloiseleur I applied your patch about the copy-merged-cells,I don't completely understand your Nested loops,I think it can be removed,what do you think about it?
Hi maoyg, This nested loop was for a use case like this : * Fill B1 and B3 with a value * Merge A2 -> C2 with a value In OOo 2.1.0, when you select the column from B1 to B3, the merged A2:C2 range was also selected. So I was forced to clean (ie : unmerge it) before applying the copy. It does not seem to be the case anymore in recent versions of OpenOffice.org. So Yes, I agree with you : it can be removed. Regards,
Hi mloiseleur, thanks for your comment!I think Niklas can integrate it in OOo3.2 version.
Hi mloiseleur, this patch exist some questions,you should see Niklas's comment in 2007/10/16 16:01:13.
Created attachment 60297 [details] The patch file is about i7500#-v1.
Accepted.
I added the patch to CWS "calc49".
reassigning to QA for verification
Created attachment 62256 [details] TestCaseSpecification
verified in internal build cws_calc49
Fixed in DEV300m49 on WinXP Closing