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
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
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.
Created attachment 83373 [details] attachment-23497-1.dat
Created attachment 83374 [details] FILE 2.ods
Created attachment 83375 [details] FILE 1.ods
(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).
(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!
Though this is mainly the area where Markus is working, IIRC, I think Kohei may be interested too because of the on-going rework..
** 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 (4.4.1 or later): 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 your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-07-18
** 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
Dear ADRIAN W, 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. 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) from https://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: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Not sure from the report, but may be related to: https://bugs.documentfoundation.org/show_bug.cgi?id=45385
Not reproducible anymore. Named ranges copied along when copying content from FILE2 Sheet1 are added to FILE1 and do not replace any existing name. Probably fixed with the work of a dozen changes in March and April 2016 to fix bug 75372 and bug 76523.