When I edit a cell formula like "=IF(OR(t<ISTART,t>SHUT),0,QL1_)", the cells that have names (t, ISTART, SHUT, QL1_) are not highlighted in the formula, and not highlighted in the cells that are referred to. If I *don't* used named cells, highlighting works normally, as expected. This lack of cell highlighting reduces the usability of Calc with named cells. I think it might be something to do with some parser that is being used to identify cell names, and that parser is not clever enough to check for names cells. It's worse, actually. Here is where there is a true bug. If using named cells that can be parsed/interpreted as referring to cell references, eg "ES1", then LibreOffice will highlight the name ("ES1") in the formula, and will also highlight cell ES1, even though the calculation engine is smarter and realises that ES1 is a name and refers to something else.
I have managed to reproduce the lack of highlighting for named cells in a formula, but I cannot test the case where a cell is named "ES1"–or any other name following that pattern–because Spreadsheet returns an error of "Invalid name." This seems like the expected behavior, given what the help docs have to say on valid cell names. Perhaps this is really two separate bugs?
Created attachment 52510 [details] Screenshot showing wrong interpretation of name
You might be right, maybe this is two bugs. I use "alt-I N C" to Insert -> Names ->Create (then click 'top row' in the dialog). In the attached screenshot, I created the name "AA1" using a label above the cell D3. When I move the cursor to cell D3, it shows the resulting generated name. In previous versions of LO/OOo, I'm pretty sure this showed as "AA1_" in cases where the specified name corresponded to a cell label. Then, if I create a cell with the formula "=AA1" then I get the contents of cell AA1, not the contents on the named cell. This is really a separate bug, I guess, in the Insert -> Names -> Create functionality (failing to give a warning when an invalid name is created).
Created attachment 52607 [details] Screenshot showing properly referenced named cells. You get no error when you name a cell using that method of naming a cell because it silently averts the error by naming it "_AA1" instead of "AA1". So there is only one bug here, that being the failure of Calc to highlight named cells. The naming process is working as it should, unless you count that silent error aversion to be a bug.
I am marking this bug as relevant to all architectures because I encounter it on an x86_64 machine. I am also lowering the importance because all of the values and calculations will still work properly, the problem is only aesthetic.
Created attachment 52614 [details] Auto-renamed cell -- very hard to spot
On my Ubuntu 11.10 machine, the automagically added underscore prefix is very hard to see. I didn't see it until I went looking. LibreOffice is changing my label without telling me, and then when I attempt to use my label in a formula is highlighting it as though it has 'understood' the label, when in fact it is highlighted a cell somewhere way over to the right, off-screen. I think this is more than just an aesthetic problem, at best I'd say its a misleading usability problem. Debugging spreadsheets is hard at the best of times, and the loss of accurate highlighting with named cells makes this much harder when LibreOffice silently renames names... The changes that would personally make me really happy would be, for example * visual warning about cell names being renamed (eg a Comment being added to first-named cell to alert the user that the name is not what was requested) * fix the parser that deals with formula highlighting so that it consults the name list as well as simple cell coordinates. Perhaps this is tricky because you can have a named column, but use of a name refers only to a single cell in the column.
Also, this is still a problem in the current git master branch for x86_64. It could be a problem in git builds on x86 systems as well, however, I am not running any x86 systems and cannot test that.
[Reproducible] with "LibreOffice Portable 3.3.3 - WIN7 Home Premium (64bit) German UI [OOO330m19 (Build:301 Tag 3.3.3.1)]". Steps to reproduce: 1. penn attached "sample.ods" 2. Double click 'D3' As expected referenced Cells B2, B3 get colored borders and references in formula "B2" and "B3" are shown with colors of cell borders. 3. Double click 'D4' Unexpectedly referenced Cells B2 (name = NamedCell1), B3 (name = NamedCell2) will NOT get colored borders and references in formula "NamedCell1" and "NamedCell2" are NOT shown with colors of cell borders. Might be an other aspect of "Bug 41304 - EDITING: Cell formula shown without colors, colored frames missing around referenced cells"
Created attachment 52671 [details] Sample document, see Comment 10
Old problem, see Comment 10! Still problem with Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) English UI [(Build ID: d3d1481-3f8994a-2ba0a9f)]" (110909) @Kohei: Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
I'm not sure that this is really a bug. The feature correctly is called "Named Expression" because can not only assign ranges to them but also formula parts. The question is if it really makes sense to highlight named expressions. I'll add Christoph as UX guy into this.
(In reply to comment #13) > I'm not sure that this is really a bug. The feature correctly is called "Named > Expression" because can not only assign ranges to them but also formula parts. > The question is if it really makes sense to highlight named expressions. Thank you, Markus. I think the feature would work much better if ... If the formula contains a named expression, then ... * If the named expression is a named range, then highlight the named range both in the formula (color coding) and the document (boundary around cells). * If the named expression is a named formula, then don't use highlighting. (Note: In future version we might be more clever and show the formula via e.g. a tool tip) I think the hardest part is that the normal cell ranges that get highlighted get a small "handle" in the lower right, so that the user can easily resize the cell range. I assume that this is close to impossible for Named Ranges, so that we need to omit that "handle" ... or think about a clever substitute. However, although we might not offer everything, I think that the description (if case) above might already help a lot for the default use case of naming cell ranges. But, I might not be aware of any side issues like "Tools - Detective". Furhtermore, I assumed that Calc can correctly differentate between real cell references and named ranges (please see the last paragraph of comment #0). Hope this helps. Christoph
(In reply to comment #14) > However, although we might not offer everything, I think that the description > (if case) above might already help a lot for the default use case of naming > cell ranges. But, I might not be aware of any side issues like "Tools - > Detective". Furhtermore, I assumed that Calc can correctly differentate between > real cell references and named ranges (please see the last paragraph of comment > #0). > Last Paragraph of comment #0 is invalid. Neither does Libreoffice allow RangeNames with the name ES1 nor does ODF allow that. All names that would be interpreted as references are not valid named ranges. A short explanation for the current behaviour: In ScInputHandler::InitRangeFinder() we parse the cell string for references. This feature has been designed excatly like it behaves at the moment So the current behaviour is expected and I'll mark this bug as enhancement. If one wants to improve it, it might be a nice idea to create a concept for functions, values, database ranges, ... I'll add it to my enhancement list for (local)range names. Maybe I find some time for the 3.6 release.
(in reply to comment #15) The last paragraph was indeed incorrect, but only as a result of another bug, which is that when setting a name for a cell like "ES1" (I use the alt-I-N-C key sequence) there is no warning given to say that the name applied did not match the name provided. The result is that one then uses the name, but finds that it refers to a cell in column ES, rather than the cell with the label "ES1" above it. A more complete handling of named cells would be a very great aid for debugging spreadsheets of this kind. Current highlighting is rendered useless if named cells are adopted.
Since all new unconfirmed bugs start in state UNCONFIRMED now and old unconfirmed bugs were moved to NEEDINFO with a explanatory comment, all bugs promoted above those bug states to NEW and later are automatically confirmed making the CONFIRMED whiteboard status redundant. Thus it will be removed.
Please read this message in its entirety before responding. Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed. If you have time please do the following: 1) Test to see if the bug is still present on a currently supported version of LibreOffice (preferably 4.2 or newer). 2) If it is present please leave a comment telling us what version of LibreOffice and your operating system. 3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System Please DO NOT 1) Update the version field 2) Reply via email (please reply directly on the bug tracker) 3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Adding self to CC if not already on
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.