Bug 41915 - named cells not highlighted when editing formulae
Summary: named cells not highlighted when editing formulae
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.3 release
Hardware: All All
: low normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2011-10-18 00:10 UTC by john
Modified: 2019-09-19 14:31 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot showing wrong interpretation of name (73.39 KB, image/png)
2011-10-18 23:24 UTC, john
Details
Screenshot showing properly referenced named cells. (60.55 KB, image/png)
2011-10-20 13:56 UTC, Michael Dewitt
Details
Auto-renamed cell -- very hard to spot (13.54 KB, image/png)
2011-10-20 23:26 UTC, john
Details
Sample document, see Comment 10 (8.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-10-23 23:03 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description john 2011-10-18 00:10:16 UTC
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.
Comment 1 Michael Dewitt 2011-10-18 21:29:44 UTC
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?
Comment 2 john 2011-10-18 23:24:52 UTC Comment hidden (obsolete)
Comment 3 john 2011-10-18 23:28:03 UTC Comment hidden (off-topic)
Comment 4 Michael Dewitt 2011-10-20 13:56:10 UTC Comment hidden (off-topic)
Comment 5 Michael Dewitt 2011-10-20 13:59:29 UTC Comment hidden (off-topic)
Comment 6 john 2011-10-20 23:26:06 UTC Comment hidden (off-topic)
Comment 7 john 2011-10-20 23:31:34 UTC Comment hidden (off-topic)
Comment 8 Michael Dewitt 2011-10-21 08:33:30 UTC Comment hidden (obsolete)
Comment 9 Michael Dewitt 2011-10-21 08:33:50 UTC Comment hidden (obsolete)
Comment 10 Rainer Bielefeld Retired 2011-10-23 23:01:44 UTC
[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"
Comment 11 Rainer Bielefeld Retired 2011-10-23 23:03:01 UTC
Created attachment 52671 [details]
Sample document, see Comment 10
Comment 12 Rainer Bielefeld Retired 2011-10-23 23:05:37 UTC
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.
Comment 13 Markus Mohrhard 2011-11-04 18:03:42 UTC
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.
Comment 14 Christoph 2011-11-06 15:20:04 UTC
(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
Comment 15 Markus Mohrhard 2011-11-06 19:12:59 UTC
(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.
Comment 16 john 2011-11-06 19:51:10 UTC
(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.
Comment 17 Björn Michaelsen 2011-12-23 13:23:45 UTC Comment hidden (off-topic)
Comment 18 Joel Madero 2014-07-13 01:33:37 UTC Comment hidden (obsolete)
Comment 19 Alex Thurgood 2015-01-03 17:38:10 UTC Comment hidden (no-value)
Comment 20 Ulrich Lukas 2016-05-01 02:45:22 UTC
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"..
Comment 21 john 2017-03-16 05:47:06 UTC
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.
Comment 22 john 2017-03-16 05:48:53 UTC
Sorry, in comment #21, I inteded to say that I 'agree with comment #15'.
Comment 23 Luke 2018-04-28 18:48:54 UTC
Named cells still not highlighted in Version: 6.1.0.0.alpha1+ (x64)
Build ID: a3783c0af4bd21eb9c001aadc60c660c06a47779
Comment 24 Adam 2018-10-10 21:37:19 UTC
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?
Comment 25 john 2019-09-19 14:31:45 UTC
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