| Summary: | Convert external references in named ranges to static values when breaking the external linkage. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Rémy Meunier <remy.meunier> |
| Component: | Calc | Assignee: | Kohei Yoshida <kohei> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | gerard.fargeot, jbfaure, kohei |
| Priority: | medium | ||
| Version: | 3.3.1 release | ||
| Hardware: | Other | ||
| OS: | Windows (All) | ||
| Whiteboard: | target:3.4 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: |
Ghost link created on openning
Created link before any change of the original file Created links after changes of the original file - ie File supply originaly with this note empty file with ghost link Message receive with no external link but ERR:520 in the file Version of LibO |
||
Could you provide more infos on the link you see? I can't see any link in the document after opening it here (macros enabled or not, links updated or not). I can't reproduce neither on 3.3.1 nor master branch. (In reply to comment #0) > I joint a file doing it. To target on the problem, macros and sheets has been > erased from the original file. Do you have these links before deleting the sheet containing "precedents" of the 2 formulae in I3 and I4 (CHOISIR(....)) ? See https://bugs.freedesktop.org/show_bug.cgi?id=31741 Created attachment 44031 [details]
Created link before any change of the original file
Created attachment 44032 [details]
Created links after changes of the original file - ie File supply originaly with this note
Created attachment 44033 [details]
empty file with ghost link
Starting with the original file, I delete everything and create a new Sheet called Feuille1. This empty file still shows a ghost link as showed.
Your document has lots of named ranges pointing to external documents. Those are creating the links on file load. Go to Insert - Names - Define, and look through those names and their references. One step ahead. Good. Here is what I found following your observation Mr Yoshida. The original file does not have any range pointing outside any sheets of the file itself. So, no external links (ref. attachment 44031 [details]). However, when I erase all the range of the empty file created starting with the original one (ref. attachment 44033 [details]), the ghost link disappear. Bravo! You are right, there is a few range name define in this application. Let me find out witch one is the bad guest and get back to you with more information asap. Regards. The source a the problem is found (thank's to gerard24). The original file do have a sheet used as a working area. This sheet is not use directly by the application. It is really just a working place for manual data transfert (copy & paste) or data referency for scenarios. No named ranges are pointing to the cells of this sheet. However, some cells of this working sheet do have the value #REF! Plus, it refer exactly to the employés.f link shown (ref. attachment 44031 [details]). The bad cells have the value ERR:520 and shows exactly "=employés.f#ref !" as contenant. Of course, it is a bad internal referency forgot there (sorry....). But it have no impact on the real application as I said. This doesn't cause any problem with the version 3.2.1 but it seems to be notice and keep in mind by the new version 3.3.1. The correction of theses cells eliminate the apparition of what I called earlier the "ghost link". In fact, the source of the problem and a way to solve it has been found but I do have an hesitation to mark this note's status as RESOLVED. First, should a persistent link appear at the openning of a file when a cell sheet have a value "=MySheet.x#REF!" somewhere? Second, when I deleted everything but the named range, the named ranges stay and get the form "'file:///C:/Documents and Settings/Administrateur/Mes documents#REF !'#$'#REF !'.$F$11:$F$35". Should it let behind a persistence link as shown in attachment 44033 [details] or should the named ranges refered to a specific sheet be erase at the same time a sheet is erase? My opinion is, no sheet, no more named ranges refered to it. So is this then not a bug, but a case of garbage in--garbage out? Please note what there is no problem with the preceeding version using exactly the same "garbage". But I agree. There is some unused cells with ERR:520 value. But these are internals values. No external links at all..... Whatever, what about the case of complete erase of sheets. Shouldn't erase the named range refered to them too? After all, a named cell cannot refer to 2 different sheets at the time. Regards. Rémy, that an earlier version has behaved in a certain way on bogus input data (if that is what it is) doesn't mean that all future versions need to do it, too. After all, if you want nothing to change, don't change anything, don't ever upgrade any software. Kohei, what is your opinion, a bug or an appropriate reaction to bogus input? Honnesly, I don't know if the "ghost link" should be tag as a bug or not. As a user, I only want to be able to find my way out easily when something wrong happend. When this particular problem is known, the solution is easy. But it was not intuitive for me, a user, to get on the right direction to solve it. I have a feed back about external links when there is no external links at all in the original file. Even after having deleted all sheets in the file, the feed back remains !!!! With respect, is it the user's job to understand what is in the "black box" to get back on there feet? So I am only saying that the previous version handle the problem by itself. I am not speaking about resistance for changes nor that I want to get too personnal. I am speaking about confusion for the average user to solve the problem with this kind of feed back. Facing the problem, I was not looking on the right direction to be able to solve it. And I may say that I am a user with knowledges quite a bit over the average. Regarding the named range, my opinion is the same. It should get out when the corresponding sheet is erased. Regards. So, what exactly is a bug reported here? There are lots of rants it's hard to read.... I'm afraid things are not that simple. What if a named range references two sheets, and only of them gets deleted? Also consider the fact that when a cell references a sheet, and the sheet gets deleted, we still keep the cell content except that the cell now shows #REF!. Named ranges are treated like cells (except that they aren't on sheets), so if we decide to remove named ranges on sheet deletion, we are in fact introducing inconsistency. And some users may want to keep the named range but have #REF! displayed so that he or she can fix the referenced sheet after accidental sheet removal. On that note, removing named ranges may be considered a "data loss". One thing I will consider is staticizing the value of external references when the linkage to that document breaks. We already do that in cells, so we should do the same for named ranges too. Changing the summary. Hopefully I can find time to do this for 3.4, but the schedule is getting tight... Created attachment 44229 [details]
Message receive with no external link but ERR:520 in the file
And that one is already fixed as GerardF said in Comment #2. Take a look at Bug 34930. Thamk's for the follow up Mr Yoshida. If I may.... Should I create another subject maybe called "Wrong external link feed back cause by ERR:520" (please see attachment External link). This dialogue box is shown when there is ERR:520 in a cell (#REF! value somewhere in the file - A #REF! value in a Named range do the same but if I undersstand well, you will handle the Named range referency forcing a static value, isn't it?). Receiving this feed back at the opening of the file gets the user confuse. To solve it, the user is looking for external links when there is no external links at all. If a static value solve the problem, It seems that there is no such value assign to the cell having a #REF! value. > And that one is already fixed as GerardF said in Comment #2. Take a look at > Bug 34930. I meant to say Bug 31741.... (In reply to comment #20) > Thamk's for the follow up Mr Yoshida. > > If I may.... > > Should I create another subject maybe called "Wrong external link feed back > cause by ERR:520" (please see attachment External link). No need. That should have been fixed already. Are you sure that's still happening? I will double-check that when I work on this one. Created attachment 44241 [details]
Version of LibO
It is happening with this version of LibO.
Ok. In 3.4, when breaking a link, it staticizes all cells containing named ranges that point to the linked document, and removes those named ranges as well. However, this still doesn't work with cells and named ranges containing reference errors, but that's a very hard corner case and I'm afraid I can't fix that one for 3.4. Ok. I'll mark this fixed. (In reply to comment #24) Thank's a lot for your effort and follow up. Base on your comments below ... > However, this still doesn't work with cells and named ranges containing > reference errors, but that's a very hard corner case and I'm afraid I can't fix > that one for 3.4. Whatever the difficulty level, if you think that this case deserve to be fix, should this tickey marked as "Pending" instead of fixed? Once again, thank's a lot. (In reply to comment #25) > Whatever the difficulty level, if you think that this case deserve to be fix, > should this tickey marked as "Pending" instead of fixed? > > Once again, thank's a lot. If you don't mind, could you file a new bug for that? |
Created attachment 44021 [details] Ghost link created on openning When openning certain files, a link is created. However, the file has no external link (working properly on version 3.2.1). Impossible to erase the link using the usual commands. The link reappear on each openning. I joint a file doing it. To target on the problem, macros and sheets has been erased from the original file.