When a reference to a non-existing sheet is being entered into a formula or when a sheet is renamed/deleted, LibreOffice substitutes the reference that pointed to that sheet with text "#REF!". This makes correcting formulas difficult or even impossible since the old value is lost. How to reproduce the bug: Create a new spreadsheet and enter "=SheetX.A1" into any cell. Calc will change the formula to "=#REF!.A1" immediately! Calc should display "#REF!" in the cell but the content of the cell should be untouched. Highlighting the reference that causes the error would be a helpful feature.
Bug still in 3.5.1.2 This shouldn't be too difficult to fix?! Displaying "#REF!" but leaving the formula untouched is already implemented with other error messages like "#NAME?".
Thanks for bugreport Reproduced in 3.5.3 on Fedora 64 bit, but not reproduced in 3.3.4 therefore regression
This was already in OOo 3.3.0 the case and probably integrated from there, removing regression keyword. However, I agree that input could be preserved in this case.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1135bfd78802e5c40ca09bcbc75d0908a423872a fdo#46338 preserve sheet name input of invalid reference
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-3-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cf6bbda7a7f907f6604a97c7b440880e933281c2&g=libreoffice-3-6 fdo#46338 preserve sheet name input of invalid reference It will be available in LibreOffice 3.6.
Thanks for fixing this bug
Yes, thanks a lot! This is very much appreciated!
Hi, I am currently using libreoffice in version 3.6 and this fix hasn't work. I am using 3.6.3 version, from debian's experimental branch. I have been trying to create a template that allows, after inserting from csv file a sheet, viewing those datas graphed (obviouly always imported the same way). And I have found that it isn't enough to have unchecked the real time update of cells etc. As when I press F9, it gets #REF!-ed. I have seen that when I delete the sheet, it will get as #REF! in the formula. Even if I instert later a sheet and rename it with before's name, will still be in #REF!. Thank you very much, Javier Domingo
Confirmed in 3.7.0.0 alpha0! The original input "=SheetX.A1", causing substitution with text "#REF!" is fixed. Even though the cell displays "#NAME?" instead of "#REF!". But deleting a sheet results in the old behaviour of replacement with text "#REF!"!
There currently is no way that the formula could "remember" the name of a sheet that is deleted because it never knows the name. The formula holds the position of the sheet, for displaying the formula the name of the sheet is obtained from the document (hence when a sheet is renamed that's immediately available in formula display). When the sheet is deleted there is no name anymore that could be displayed. Closing this bug again. The fix was about that when entering an unknown sheet name the name is changed to #REF! as well but in that case the name can be preserved.
(In reply to comment #10) > There currently is no way that the formula could "remember" the name of a > sheet that is deleted because it never knows the name. The formula holds the > position of the sheet, for displaying the formula the name of the sheet is > obtained from the document (hence when a sheet is renamed that's immediately > available in formula display). When the sheet is deleted there is no name > anymore that could be displayed. > > Closing this bug again. The fix was about that when entering an unknown > sheet name the name is changed to #REF! as well but in that case the name > can be preserved. So I can create all graphs in the template if I never create the refering sheets? I mean, once I have created the, I won't be able to erase them. Correct me if I misunderstood...
Sorry, but I don't get why it shouldn't be possible to keep the "name" of a deleted sheet! In my example, "=SheetX.A1" is saved as text with lower case letters, because there is no SheetX and Calc cannot set a pointer and retrieve the name for that pointer (at least that is how I interpret your explanation). Why can't that be done with references, when a sheet was deleted? Before a sheet is deleted, all pointers to that sheet should be replaced with the "text sheet name". After deletion, the affected cells will display "REF!" or "NAME?" or "WHATEVER", but the name of the old sheet is still there as information for the user! And when I paste a sheet with the right name into my document, all formulas should be updated and the cells without pointers get a new pointer assigned when the "text name" matches the inserted sheet's name. I am not sure if I agree with you closing this bug. Your fix does not fix what I reported, which includes deleted sheets. (Even though I highly appreciate your work!)
(In reply to comment #11) > So I can create all graphs in the template if I never create the refering > sheets? I mean, once I have created the, I won't be able to erase them. No, you can't have a formula that refers a non-existing sheet. Even though the input of an unknown name is remembered, it is just a string that will not automatically change to a reference when you create a sheet with that name.
(In reply to comment #12) > Sorry, but I don't get why it shouldn't be possible to keep the "name" of a > deleted sheet! I did not say it was not possible, it is currently not implemented that a formula could know the name after a sheet had been deleted. That is not a bug, you're asking for a new feature. Please open a request for enhancement.
Wow, LibreOffice at its best again! So close to Microsoft, but not quite ... it's not a bug, its a missing [sic] feature! My bug description was probably not 110 % correct, and your fix actually fixes the bug. But it does not make anything any better! The problem was mostly with #REF! from deleting columns/rows/sheets, because that can cause missing references that go unnoticed for a long time ... and by then, the user has forgotten what the reference once was pointing to. I will not file a feature request. That would make bugzilla even more chaotic as it is already ... and quite frankly, I care less and less. Great how you try to attract Microsoft users and scare away old OpenOffice/LibreOffice users!