[Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

[Kohei Yoshida]

On 07/23/2013 07:00 AM, Eike Rathke wrote:
> Hi Kohei,
>
> On Thursday, 2013-07-18 08:25:45 -0400, Kohei Yoshida wrote:
>
>>> * The actual label name displayed is taken from a cell's content,
>>>    formula expressions using a label automatically change their display
>>>    label names whenever that cell content is changed.
>>>    * This is not possible with named ranges.
>> Sure. But is this *that* important to users?  To me the whole label
>> range implementation is such a duplicate functionality for very
>> little marginal difference, and I'm not really sure if that
>> difference even matters.
> For those who use it it probably is important ...
And the original goal of my post was to figure out whether there are 
people who use it, and if yes, what proportion of users (roughly), and 
whether or not their use cases could be possibly fulfilled by either 
named ranges or database ranges.  Somehow I'm not getting any feedback 
on that front.

> anyhow, this is even
> part of ODFF, so we somehow should support it.
We should somehow *handle* it during import, yes. Presence of it in the 
ODF spec does not automatically dictate that we should have the feature 
available in the UI, for run-time use.

> What is debatable is the
> "automatic label lookup" that IMHO should be deprecated and the default
> configuration setting be disabled.
OK.

>
>
>>> * One label names exactly one row or one column, expressions or
>>>    multi-column/row ranges are not possible.
>>>    * The named expressions dialog could restrict that though.
>> I don't see how that restriction could be useful.  You can define
>> one column / one row only named ranges (or database ranges for that
>> matter).  Is there a use case where having this restriction is
>> useful in real life?
> It is needed for the intersection of row and column labels, that works
> only with vectors, e.g.  ='Sales' 'Hamburg'
Understood.  But again, you are talking about implementation details 
here.  I was hoping for an argument that's more usability-oriented, 
hence my motivation to CC it to ux-advise, and the emphasis on "use case 
in real life".

>
>
>>> * The label name can include spaces and other arbitrary characters that
>>>    in a formula expression would have special meanings, using such a name
>>>    in an expression is possible by enclosing the entire label name in
>>>    single quotes. A label name can even be a string that otherwise would
>>>    be a cell reference.
>> Yes.  And the fact that this can be a string is actually very scary
>> to me.  This potentially makes tracking references very difficult
>> without sacrificing performance.  Dropping it would enable us to
>> optimize it further.
> The performance bottleneck is the automatic label thing where the
> sheet's content is searched for a string; searching just a few defined
> label ranges (if any) doesn't make much difference compared to named
> ranges.
*If* there are only a few defined label ranges used per document. Such 
assumption (or hope) can be very fragile in reality and I tend not to 
make such assumption especially when spreadsheet documents tend to 
become very large very easily.

>
>
>>>    * A named range currently has to consist of alphanumeric+underscore
>>>      characters and can't resemble a cell reference.
>>>    * ODFF does provide means to store usage of such non-simple names
>>>      though with $$SingleQuoted but we need to implement that in the
>>>      formula compiler (anyway), see
>>>      http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017964_715980110
>>>
>>> Furthermore we probably could use exactly the Label functionality for
>>> the GSoC "Enhanced Database Ranges" Table feature when it comes to
>>> in-Table formula expressions adressing the Table's rows or columns.
>>> Actually it would be necessary to support identical label names for
>>> different Tables (ranges) within one sheet, again this is not possible
>>> with named ranges.
>> I'd rather we extend the database range code to support these
>> missing bits rather than piggybacking on top of the label range
>> code.  I don't see it as a reason why we need to keep label range.
> I meant the special Excel cell formula syntax for formulas in cells of
> a Table that address rows/columns/intersections of the Table by their
> header names. That is very similar to defined labels
> compiler/interpreter-wise.
Sure. But this is an implementation detail that's not the focus of my 
original post.  Whether or not we could re-use the label range 
implementation for the enhanced database range feature is a topic for 
another discussion, and one that doesn't have to involve our UX experts.

> Of course it doesn't matter where we actually
> stick the "defined label" in, having them as part of the database range
> probably is best because we usually can derive it from the top-row of
> the database range (don't know currently if Excel allows more than one
> row for those Table labels, they did a very awkward thing with their
> labels back then).
Noted.  Perhaps it was that awkward-ness that prompted them to drop this 
feature in 2007?  I'm just guessing.

Kohei

-- 
Kohei Yoshida, LibreOffice Calc hacker, SUSE.