Issue 72191 - Support variable formula conventions
Summary: Support variable formula conventions
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: recent-trunk
Hardware: All All
: P3 Trivial with 24 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Blocks: 20857
  Show dependency tree
Reported: 2006-12-01 18:56 UTC by jodygoldberg
Modified: 2013-08-07 15:13 UTC (History)
13 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---

Proposed UI selector for addressing type. (80.28 KB, application/vnd.oasis.opendocument.text)
2008-01-26 10:36 UTC, kpalagin
no flags Details
Updated to accomodate requests (80.99 KB, application/vnd.oasis.opendocument.text)
2008-02-11 20:41 UTC, kpalagin
no flags Details
Updated (83.91 KB, application/vnd.oasis.opendocument.text)
2008-02-12 08:14 UTC, kpalagin
no flags Details
Updated spec to new selector control (83.89 KB, application/vnd.oasis.opendocument.text)
2008-02-21 09:32 UTC, kpalagin
no flags Details
formula convention (syntax) option page (65.49 KB, image/png)
2008-07-21 18:25 UTC, kyoshida
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jodygoldberg 2006-12-01 18:56:50 UTC
The core changes to enable cell formulas to be displayed using XL style R1C1 and
A1 notation are in place.  However, there are no use visible hooks for selecting
which of the 3 current styles to use
- OOo
- XL A1 ([book]Sheet1:Sheet2!A1:A3  and A:C)
- XL R1C1 ( ....!RC:RC[2])

there is a potential to eventually add Lotus style references here
and if possible to use this selection to choose the entire formula convention
rather than just the address spec.
    - Use a locale specific argument separator foo(a,b,c)

Could someone on the usability/UI team make a proposal ?
Comment 1 frank 2006-12-01 21:17:55 UTC
Hi Matthias,

please comment on this one and proceed as needed.

Comment 2 kpalagin 2007-03-17 23:26:35 UTC
Dear developers,
any progress with this issue?
Thanks a lot for your attention.
Comment 3 Martin Hollmichel 2008-01-22 07:49:42 UTC
move target from 2.x to 3.0
Comment 4 Martin Hollmichel 2008-01-22 07:57:11 UTC
reassign to fl, set target 3.x, any volunteer to provide a proposal ?
Comment 5 pmike 2008-01-22 10:51:02 UTC
As a simple proposal: in main menu
[View] - [Addressing (or Notation?)] - then radio-mark-checked submenu:
Classic OOo
Excel A1
Excel R1C1
Comment 6 kpalagin 2008-01-26 10:36:40 UTC
Created attachment 51170 [details]
Proposed UI selector for addressing type.
Comment 7 kpalagin 2008-02-05 20:57:05 UTC
So could somebody from developers take a look at my spec and either comment or 
proceed with implementation?
Thanks a lot!

Comment 8 hashproduct 2008-02-11 01:37:04 UTC
I strongly object to the names of the cell reference styles ("Absolute",
"Relative (R1C1)", "A1", "Lotus") because they do not make the differences among
styles clear.  It is meaningless to describe styles as "absolute" or "relative"
(and hence the third sentence of the first paragraph of section 1 is wrong)
because an absolute or relative reference can be written in any of the styles
(e.g., $A$1, A1, R1C1, RC).  "A1" and "R1C1" are good; the names additionally
need to distinguish among the OOo, Excel, and Lotus variants of A1.  How about
"OOo A1", "Excel A1", "Excel R1C1", and "Lotus A1", along the lines of pmike's
suggestion?  Or the menu could give an example in each of the styles (like the
comments in "enum Convention" in source file "sc/inc/address.hxx"), followed by
"(OOo)", "(Excel)", or "(Lotus)" as appropriate; the full-generality examples
may be helpful to some users but overwhelm others.

Also, I suggest moving the choices into a submenu "Format -> Sheet -> Cell
Reference Style" so that it is clear to users what they are choosing.
Comment 9 ooo 2008-02-11 11:34:22 UTC
I second that the naming (Absolute, Relative, ...) doesn't make sense. It should
be A1 vs. R1C1 instead. As the current implementation differs between OOo_A1,
XL_A1 and XL_R1C1, where  XL_... includes usage of the '!' sheet name separator
as well, technically only these could be the options offered at UI. The Lotus
style isn't much more than experimental. I also think this should not go into
the Format menu, the options are not related to formatting. In fact it shouldn't
go into any of the main menus, these easily get too crowded, and it is not an
option you'd need to switch frequently. IMHO it should go somewhere under
Comment 10 kyoshida 2008-02-11 12:48:52 UTC
>IMHO it should go somewhere under Tools.Options.Calc

I agree with er here.  Perhaps Tools.Options.Calc.Calculate would be a perfect
place for this?
Comment 11 kpalagin 2008-02-11 20:41:19 UTC
Created attachment 51460 [details]
Updated to accomodate requests
Comment 12 kpalagin 2008-02-11 20:55:43 UTC
Please see updated spec (sorry for colors and fonts - could not match on 
If possible, could somebody specify i-Team Members?
Thanks a lot.
Comment 13 hashproduct 2008-02-11 23:32:07 UTC
Two nits in the updated spec:
- The first entry under "Suggested accelerator keys" should say
"", not "OpenOffice".
- In section 3, "Configuration", it isn't clear which style "standard
addressing" refers to.  If it's A1 style, say that.

Also, I'm wondering why OOo Calc has Excel and variants of A1 but
only one kind of R1C1, which (based on the comment in address.hxx) seems to be
the analogue of the Excel A1.  I prefer R1C1 on its merits and don't care
personally about consistency with Excel; thus, if there were an
R1C1, I would use that.  Does the lack of an R1C1 indicate that
the OOo team has decided to adopt the Excel formats as recommended and just keep
the A1 for OOo backward compatibility?  If so, perhaps the A1 should be labeled "Classic" or even "Legacy".
Comment 14 hashproduct 2008-02-11 23:36:29 UTC
The third sentence in Section 1, "Detailed Specification", is still wrong.  It
should say "A1 or R1C1", not "relative or absolute".
Comment 15 kpalagin 2008-02-12 08:14:06 UTC
Created attachment 51469 [details]
Comment 16 hashproduct 2008-02-12 18:24:41 UTC
The updated specification looks good to me.  There are a few typos that don't
affect the meaning: "R1C1addressing" in the Abstract needs a space, the
Migration section needs a period, and "Default  setting" in Configuration has an
extra space.  I don't know if you care about fixing them.
Comment 17 kyoshida 2008-02-12 19:43:25 UTC

Let's put it this way.  OO.o A1 style is what we have natively in OO.o today, so
we're stuck with it.  Excel A1 style is what the majority of spreadsheet users
are used to, so we have a good reason to support it.  The same reasoning applies
to Excel R1C1.  However, because there is no prior R1C1 equivalent address
convention in Calc, I see no reason to add such support.

If it were entirely up to me, I would just implement the Excel R1C1 without
adding yet another R1C1 style address convention just to be different from
Excel.  Lesser number of styles means lesser effort to maintain the code.
Comment 18 hashproduct 2008-02-15 04:04:52 UTC
@kohei: OK.  I guess my question is which set of conventions is considered
"preferred" in current OOo Calc: Excel conventions or traditional OOo
conventions?  If OOo conventions, then it seems inconsistent not to offer an OOo
variant of R1C1; if Excel conventions, then I think the OOo A1 style should be
marked somehow as old/deprecated (even if it remains the default for best
Comment 19 kyoshida 2008-02-15 14:22:51 UTC
@hashproduct: It is not a take-Excel-or-stick-with-OOO-style situation.  Why do
you want to pick only one convention and deprecate the rest?  Ultimately it's
not up to us to decide; if there is sufficient demand for either one it makes no
sense to deprecate any of them.  OTOH, if the majority of the users only use one
convention and don't care about the rest at all, then there is a good reason to
deprecate the rarely-used ones.  But now is not the time to make that decision.
 Quite frankly I don't know the answer to that.
Comment 20 hashproduct 2008-02-15 14:36:57 UTC
@kohei: OK, I suppose it's reasonable to leave the three styles as they are for now.
Comment 21 kpalagin 2008-02-21 09:32:59 UTC
Created attachment 51624 [details]
Updated spec to new selector control
Comment 22 kpalagin 2008-03-02 04:59:20 UTC
any progress?
Comment 23 kyoshida 2008-03-03 14:34:31 UTC
@kpalagin: sorry I have not started it yet.  I'm on several other high-prio
issues at the moment.  I'll keep you informed.
Comment 24 kpalagin 2008-07-21 11:25:04 UTC
Any news?
Comment 25 kyoshida 2008-07-21 18:21:03 UTC
I'm starting to work on writing a spec for this feature.  Because this is a
little more than just implementing a R1C1 address convention, it needs to cover
broader range of topics.  So, I'll try to get it spec'ed out on the wiki page
with your submitted spec as the starting point.  I hope you don't mind me
switching to a wiki spec because that makes it easier for to update it.

Also, I'd like some input on the proposed UI.  I will attach a screenshot of the
current UI implementation in go-oo to see if anything needs to be changed before
upstreaming it.
Comment 26 kyoshida 2008-07-21 18:25:14 UTC
Created attachment 55259 [details]
formula convention (syntax) option page
Comment 27 kyoshida 2008-07-21 18:26:29 UTC
Changing the title to something more generic.
Comment 28 kyoshida 2008-07-21 18:27:55 UTC
I'm taking ownership, and CC'ing fl.
Comment 29 kyoshida 2008-07-21 18:28:29 UTC
accepting the feature.
Comment 30 kyoshida 2008-07-22 14:24:25 UTC
Just FYI, there are still several known issues with the Excel A1 and R1C1 parser
that I'd like to fix before adding the UI bit.
Comment 31 helenrussian 2008-09-08 16:35:10 UTC
any news?

Comment 32 kyoshida 2008-09-11 14:37:55 UTC
Yes, I'm still waiting for any input on my question in #desc26.  Or does no
input mean it's been approved?

Internally, we already have the implementation.  So, it's just a matter of
finding issues and fixing them (which we are already doing) & taking care of the
UI design bits.
Comment 33 kpalagin 2008-09-11 14:57:27 UTC
Looks fine, IMHO.
I think you should upstream it ASAP, so that we can have it in 3.1.

Thanks a lot!
Comment 34 ooo 2008-09-11 17:07:38 UTC
@kohei: in #desc26 you mentioned "switching to a wiki spec", I didn't find any
though, is the attached document the spec we have?
Comment 35 kyoshida 2008-09-11 17:15:43 UTC
No the spec is not there yet.  Haven't had the time.  But the question regarding
the UI is a separate matter.
Comment 36 kyoshida 2008-09-11 17:24:03 UTC
BTW the spec will be at

It's still empty at the moment, but I will start filling it once I find the time.
Comment 37 kpalagin 2008-10-16 11:06:38 UTC
With just 6 weeks left until Feature freeze I think we will miss 3.1 as well. 
Somethin is very wrong with the project.
Comment 38 helenrussian 2008-11-07 13:57:03 UTC
I supplemented the spec. Please comment.

Comment 39 kyoshida 2008-11-07 15:00:08 UTC
@helen_russian: Looks very good!  Thank you. :-)
Comment 40 kyoshida 2008-11-07 15:03:37 UTC
The only thing is that, open issues are those issues that will *not* be
addressed when this feature is introduced.  Since this issue will be resolved
once the feature is added, we can take out 72191 from the list of open issues.

But it's not critical, you can edit it out, or I'll do it when I do my editing,
whichever comes early.
Comment 41 helenrussian 2008-11-10 12:17:39 UTC
Kohei, Eike,
please comment on updated spec.

Comment 42 kpalagin 2008-11-20 07:22:32 UTC
Kohei, Eike,
taking into account that 3.0.1 and 3.1 releases are delayed by a month do we 
have a chance of having this feature in 3.1?

Comment 43 kyoshida 2008-11-20 17:49:34 UTC
Sorry.  I know you guys want this in ASAP, but I'm starting to doubt if we can
make it for 3.1.  This does not just involve the formula syntax, but also the
separators as well (See Issue 92056).

Looking at this thread we just had on the go-oo list

We may need to change the UI a bit to allow toggling of localized separators. 
That will certainly delay inclusion of this feature upstream, I assume, unless I
hear otherwise from someone from Sun.
Comment 44 ooo 2008-11-20 18:58:31 UTC
Aiya, the fine MS selection of carefully crafted separators.. Insane.

I don't see though why the separator's issue 92056 should be involved
for this Xl-R1C1/Xl-A1/OOo-A1 issue here? They can be treated

Anyway, parsing all three conventions isn't finished yet. Current
implementation of the Xl-R1C1 convention is fairly untested. Xl-A1
convention will be implemented in CWS mooxlsc for MOOXML import, which
changes also common parts of Xl-R1C1 that in turn then will be
completely untested. Any other independent feature relying on the new
address parser implementation will not make it into OOo3.1
Comment 45 kyoshida 2008-11-20 19:02:19 UTC
>I don't see though why the separator's issue 92056 should be involved
for this Xl-R1C1/Xl-A1/OOo-A1 issue here? They can be treated

Yes, but they will likely share the same UI space in the configuration dialog. 
So, it's probably better to lump them together.
Comment 46 kpalagin 2008-11-20 19:02:28 UTC
I am not sure if we have commercial customers for 92056, but IMHO localized 
separators are at most P4 (if not P5). 92056 does not have any votes and was 
filed 4 month ago.
OTOH, original request for R1C1 have been filed 5 years ago and collected 56 
votes which tells us that our users do need it.
Delaying R1C1 by at least 6 month just because of separators is mistake IMO.
Comment 47 kpalagin 2008-11-20 20:03:56 UTC
unless we release product the code would not get testing. Our users got used 
to issues, so a bit of untested code in x.y.0 should be fine and will provide 
required testing.
Comment 48 ooo 2008-11-20 20:22:26 UTC
@kpalagin: As I said in #desc45 another reason this should not make it
into 3.1 is that the R1C1 parser will be completely untested then. CWS
mooxlsc will get integrated shortly before feature freeze. The R1C1
feature should be integrated early to some dev-milestone to be publicly
available for extensive testing before release.
Comment 49 kpalagin 2009-03-17 08:17:12 UTC
do we have any solid target for integration of this feature, like, say, m45, 
so that it gets testing?
Comment 50 ooo 2009-03-17 13:25:27 UTC
@kpalagin: No, there's no target for this. And what integration of the feature?
There isn't even a CWS having it implemented. Or, Kohei, am I wrong?
Comment 51 kyoshida 2009-03-17 13:44:56 UTC
@er: no, you're not wrong.  There is no cws for this yet.  There are still more
issues with non-default formula syntax options in the core implementation that
I'm trying to work out.
Comment 52 helenrussian 2010-03-05 12:43:49 UTC
any news?

Comment 53 kyoshida 2010-03-05 13:33:01 UTC
I'll try to squeeze this in for 3.4, together with several other related
features (variable separators, and English function names).  I've just created
koheiformula04 for that.
Comment 54 grummund 2010-07-03 15:49:04 UTC
Voting for this issue - because R1C1 cell references would be really useful.  

Not sure why this has to be a Excel compatibility mode, imho R1C1 deserves to
exist in it's own right.
Comment 55 opium13 2010-11-29 21:22:12 UTC
on attend toujours les R1C1 (et pourquoi pas L1C1),
 pour pouvoir utiliser OO.
Comment 56 kjgriffin 2010-12-01 02:37:56 UTC
Also voting for this in support of RC notation, a major block for usability, not just to be like excel.  
Comment 57 Rob Weir 2013-07-30 02:39:31 UTC
Reset assignee on issues not touched by assignee in more than 1000 days.