GNOME Bugzilla – Bug 167308
different handling of broken references to Sheets
Last modified: 2008-09-20 15:49:02 UTC
Version details: 1.4.2-2 Debian/Sarge 1) copy&paste between workbooks Source: =Sheet4!A1&"foo" Result: ="Sheet4!A1&\"foo\"" 2) remove Sheet4 Source: =Sheet4!A1&"foo" Result: =#REF!&"foo" 3) enter wrong reference via keyboard Enter: =Sheet4!A1 Result: '=Sheet4!A1 In all 3 cases I would expect the *same* result. E.g. OO results in #REF! in all of these cases. I am not sure, which result is the best solution. Result: '=Sheet4!A1 would be easy to find/replace.
The description of (3) isn't quite accurate: you are given a choice of re-editing the unparsable formula for getting a string. I don't see any situation where you would want to correct a number of these using find/replace; The GUI handling of this situation is different and the difference is *good*: we want to catch typos right away. I don't see anything wrong with (2) giving a different result. We are constrained by having to produce a #REF result, so we cannot use a string. (We could, perhaps, use a special function call like REFERR("sheet4!a1") that ignores its argument and returns #REF!. Interestingly, INDIRECT could be used.) Thus, as far as consistency goes, I see no problem whatsoever. For reference, here's what XL does: 1. =[Book4]Sheet4#REF! 2. =#REF!a1. 3. Queries for a file to open, presumably looking for Sheet4. If cancelled, it actually lets the =Sheet4!a1 stand and it results in #REF!. (3) looks weird, but at least allows one to correct a sheet name typo easily. If OO simply turns it into #REF, that is not at all helpful.
Changing priority/severity to low/minor
As Morten pointed out there is no consistency problem. Yes #2 could have asny numbber of possible other solutions but it is not clear that any one of them is really better. (With sheet 4 deleted it really doesn't matter anymore where exactly the reference pointed to!)