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
This is still an issue in: libreoffice calc Version: 5.1.2.2 Source: Ubuntu packages Hi Guys, while I realise the program behaviour is as expected when looking at the source code as explained by Markus Mohrhard in comment #15, this is clearly a bug when looking at the feature description in the settings under "Tools" => "Options" => "LibreOffice Calc" => "View" where the checkbox is called "Show references in color": When enabling the "highlight-references" feature, it should not matter if the references used in a formula are in row-column/row#-column# format or in the more human-readable version using names/named cells. Again, this is from a user perspective, but if we decide this is no useful feature, then it should be disabled completely. Or the description should be changed. From my perspective, working with long arithmetic formulas in the physics/engineering field, this is one of the more annoying usability disadvantages when comparing my experience with "some other vendor's spreadsheet calculation software"..
I agree with comment #20, there isn't really a bug here as asserted in the final para of comment #0. What is surprising is that when using Insert->Names->Create (after highlighting 'name cell' on the left together with adjacent 'value cell' on the right), there is an automatic transformation of the name in some cases, eg if the name is "AA1", then the created name is not "AA1" but "_AA1". There is no warning about this, and as the screenshot shows, it can also be almost impossible to see the underscore in the modified name. But not a bug with the formula highlighting feature per se. However, if one is in the habit of named cells not being highlighted, then one may not notice and may not check that such a name transformation has been applied. Highlighting of named references would help greatly with debugging in this little corner case.
Sorry, in comment #21, I inteded to say that I 'agree with comment #15'.
Named cells still not highlighted in Version: 6.1.0.0.alpha1+ (x64) Build ID: a3783c0af4bd21eb9c001aadc60c660c06a47779
Same problem in Mac Libreoffice 6.0.2.1. This is a pretty standard feature, even Google Sheets highlights the named cells correctly when used in formulas. How could we get this bug moving?
This bug is still present in LO 6.2.6.2 Version: 6.2.6.2 Build ID: 1:6.2.6-0ubuntu0.18.04.1~lo1 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-AU (en_AU.UTF-8); UI-Language: en-US Calc: threaded
Dear john, 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
😥
Still present in current version: Version: 7.1.5.2 / LibreOffice Community Build ID: 10(Build:2) CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: kf5 Locale: de-DE (de_DE.UTF-8); UI: en-US Ubuntu package version: 1:7.1.5-0ubuntu0.21.04.1 Calc: threaded
Dear john, 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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug