Bug Hunting Session
Bug 67581 - Copying cells with named ranges from one spreadsheet to another corrupts formula references to preexisting named ranges in target spreadsheet
Summary: Copying cells with named ranges from one spreadsheet to another corrupts form...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.5.2 release
Hardware: Other All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Paste Cell-Formula Cell-Reference
  Show dependency treegraph
 
Reported: 2013-07-31 12:58 UTC by ADRIAN W
Modified: 2019-02-05 17:49 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
attachment-23497-0.html (10.19 KB, text/html)
2013-07-31 14:35 UTC, ADRIAN W
Details
attachment-23497-1.dat (1 bytes, multipart/alternative)
2013-07-31 14:35 UTC, ADRIAN W
Details
FILE 2.ods (16.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-31 14:35 UTC, ADRIAN W
Details
FILE 1.ods (137.08 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-31 14:35 UTC, ADRIAN W
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ADRIAN W 2013-07-31 12:58:01 UTC
I have a spreadsheet file "file 1" with several named ranges.  VLOOKUP formulas in this spreadsheet refer to the named ranges.

I have a second spreadsheet file "file 2" with formulas and named ranges which I want to copy into the "file 1".  There is no duplication of range names in or between the 2 spreadsheets

Both files were created in LibreOffice, in .ods format.  Neither file has ever been saved in a different format, or opened with an application other then LibreOffice

So, here is the problem:

1. Copy cells containing formulas and named ranges from "file 2" to the Windows clipboard

2. Paste from the clipboard to a blank sheet in "file 1"

Result: formulas are pasted successfully, and named ranges are created successfully in "file 1".  All the original named ranges in "file 1" are still visible and accesible i.e. click on a range name in the NAME BOX dropdown, and the correct range is highlighted

Now the bad part...

UNWANTED RESULTS:
1. The original formulas in "file 1", pointing to pre-existing named ranges in "file 1" are corrupted - their named range references have been replaced with one of the range names pasted in from "file 2"

2. If I edit the formulas to replace the unwanted range names with the original range names, they WILL NOT CHANGE.  When I press enter to confirm my formula edit, the range name I just typed in reverts immediately to the new, incorrect range name

I can't find an existing bug report for this behaviour.  I have encountered this problem three times now, with three different pairs of calc spreadsheet files
Comment 1 Cor Nouws 2013-07-31 13:29:19 UTC
Thanks for the clear description, Adrian.
I 'muted' the summary, was a bit LOUD ;)

Do you pls, for the ease of people doing QA, have some test files?
Best,
Cor
Comment 2 ADRIAN W 2013-07-31 14:35:10 UTC
Created attachment 83372 [details]
attachment-23497-0.html

Hi Cor

 
I hope I am doing the correct thing by sending the requested files with this mail?

 
Please let me know if I need to do things differently (this is my first bug report)

 
Instructions to reproduce the problem:

 
Look at the VLookups in FILE1 on the sheet "MASTER SUMMARY" columns C to I.  They refer to the named range KPLAN1

1.	Copy the content of "Sheet1", FILE2, and paste to the sheet "ROUNDING", FILE1
2.	Return to sheet "MASTER SUMMARY" and look at the formulas:  the named range "BAND_TWO" has replaced KPLAN1 (BAND_TWO is a named range, Sheet "ROUNDING", cell E7)
3.	Try editing one of the VLookups, replacing BAND_TWO with KPLAN1 - when I do this it immediately reverts to BAND_TWO, and returns ERR 502

I hope that you can see what is happening?

 
 
Regards



Adrian

 
 
 
 
 
Read our blog <http://www.brabantialife.com/> , become a Facebook <http://www.facebook.com/brabantialife> fan, follow us on Twitter <http://www.twitter.com/brabantialife> or see product images and more on Flickr <http://www.flickr.com/photos/brabantialife> 

 
 
Brabantia (UK) Limited's standard conditions of sale apply to all goods purchased from Brabantia (UK) Limited to the exclusion of any other terms and conditions. A full copy of Brabantia's standard conditions of sale can be found on our website at http://brabantia.com/en/legal-information/ <http://brabantia.com/en/legal-information/> 

 
Brabantia (UK) Limited, registered in England & Wales with company number 4936679, is authorised for the sale of Brabantia Branded products for resale in the United Kingdom only. Contact our local representatives whose details are available at our website on http://brabantia.com/en/company/offices/ <http://brabantia.com/en/company/offices/> for sales of Brabantia Branded products outside of the United Kingdom. Please note you may not supply, distribute or sell Brabantia Branded products bought from Brabantia (UK) Limited outside of the European Economic Area.

 

Price and product proposals must be accepted within 30 days to be valid.

 

 
-----Original message-----
From:bugzilla-daemon@freedesktop.org
Sent:Wed 31-07-2013 14:29
Subject:[Bug 67581] Copying cells with named ranges from one spreadsheet to another corrupts formula references to preexisting named ranges in target spreadsheet
To:Adrian Wagland <a.wagland@brabantia.com>; 
 
 Cor Nouws changed bug 67581 
 
 What 	Removed 	Added 
 CC 	  	cno@nouenoff.nl 
 Summary 	COPYING CELLS WITH NAMED RANGES FROM ONE SPREADSHEET TO ANOTHER CORRUPTS FORMULA REFERENCES TO PREEXISTING NAMED RANGES IN TARGET SPREADSHEET 	Copying cells with named ranges from one spreadsheet to another corrupts formula references to preexisting named ranges in target spreadsheet 

 
Comment # 1 on bug 67581 from Cor Nouws 


Thanks for the clear description, Adrian. I 'muted' the summary, was a bit LOUD ;)  Do you pls, for the ease of people doing QA, have some test files? Best, Cor

 

 
 
--------------------------------
 You are receiving this mail because: 
*	You reported the bug. 
 

------
Click here ( http://www.brabantia.com/int_en/legal-information/email-disclaimer/ ) for the Brabantia disclaimer.
Comment 3 ADRIAN W 2013-07-31 14:35:11 UTC
Created attachment 83373 [details]
attachment-23497-1.dat
Comment 4 ADRIAN W 2013-07-31 14:35:11 UTC
Created attachment 83374 [details]
FILE 2.ods
Comment 5 ADRIAN W 2013-07-31 14:35:11 UTC
Created attachment 83375 [details]
FILE 1.ods
Comment 6 Cor Nouws 2013-07-31 21:42:47 UTC
(In reply to comment #0)

> UNWANTED RESULTS:
> 1. The original formulas in "file 1", pointing to pre-existing named ranges
> in "file 1" are corrupted - their named range references have been replaced
> with one of the range names pasted in from "file 2"

Can confrim this.

> 2. If I edit the formulas to replace the unwanted range names with the
> original range names, they WILL NOT CHANGE.  When I press enter to confirm
> my formula edit, the range name I just typed in reverts immediately to the
> new, incorrect range name

What I found: in the window Manage names, there are single quotes around the sheet name MASTER SUMMARY.
Removing those, makes that the original range names are again used in the formulas, but then the formulas pasted from file2 are corrupted :)


(In 4.1.0.4 I had a different result - there is some problem with c&paste formulas from another file IIRC).
Comment 7 Cor Nouws 2013-07-31 21:43:57 UTC
(In reply to comment #2)

> I hope I am doing the correct thing by sending the requested files with this
> mail?

As you can see: pls don't reply per mail, but do all in the web-interface. 
But of course thanks for the files!
Comment 8 Cor Nouws 2013-08-01 07:27:58 UTC
Though this is mainly the area where Markus is working, IIRC, I think Kohei may be interested too because of the on-going rework..
Comment 9 QA Administrators 2015-07-18 17:44:45 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2016-09-20 10:18:48 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice 
(5.1.5 or 5.2.1  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and 
your operating system, and any changes you see in the bug behavior
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword


Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20160920