Bug 85063 - EDITING: Excel structured references returns error 508 on Calc
Summary: EDITING: Excel structured references returns error 508 on Calc
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: BSA target:5.0.0 target:5.1.0
Keywords:
: 45833 84819 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-10-15 14:59 UTC by Zeki Bildirici
Modified: 2016-10-25 19:17 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample Invoice file (19.04 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-15 15:00 UTC, Zeki Bildirici
Details
Sample Invoice file 2 (18.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-15 15:00 UTC, Zeki Bildirici
Details
Expense Report -1 for bug report (13.88 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-15 15:01 UTC, Zeki Bildirici
Details
Test file (csv) (3.02 KB, text/csv)
2015-05-02 11:22 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Zeki Bildirici 2014-10-15 14:59:18 UTC
Problem description: 

Hi,

I've grabbed some templates from MS Office template site(https://store.office.live.com/templates/templates-for-Excel), opening this xls files are ok with Excel, however some of the formulas return with 508 error. 

I'm uploading two invoices and a expense report as sample files.

Steps to reproduce:
1. Go to Office Templates https://store.office.live.com/templates/templates-for-Excel (You need MS Office account to open them via office Onlinei then save to your local machine, thus i'm uploading prepared samples)
2. Open it with Calc
3. It will ask for updating fields(i think it has no external link but has link to second worksheet on the spreadsheet)

For more compatibility issues, templates site is a good resource imho.

Current behavior:

Some formulas return with 508 error

Expected behavior:

Having no errors

Best regards,
              
Operating System: All
Version: 4.3.0.4 release
Comment 1 Zeki Bildirici 2014-10-15 15:00:12 UTC
Created attachment 107883 [details]
Sample Invoice file
Comment 2 Zeki Bildirici 2014-10-15 15:00:53 UTC
Created attachment 107884 [details]
Sample Invoice file 2
Comment 3 Zeki Bildirici 2014-10-15 15:01:32 UTC
Created attachment 107885 [details]
Expense Report -1 for bug report
Comment 4 m.a.riosv 2014-10-15 21:51:08 UTC
Hi Zeki, thanks for reporting.

Sample files use "Referencing cells in a table (structured referencing)" that are no supported by LibreOffice.

If you like, report a bug asking for enhancement (change the importance from normal to enhancement).

Please don't set up your own bug as NEW, is needed a confirmation by someone else.
Comment 5 Zeki Bildirici 2014-10-16 13:51:56 UTC
(In reply to m.a.riosv from comment #4)
> Hi Zeki, thanks for reporting.> Sample files use "Referencing cells in a table (structured referencing)"
> that are no supported by LibreOffice.
> 
> If you like, report a bug asking for enhancement (change the importance from
> normal to enhancement).
Yes, sure and done. Thanks.
Comment 6 ign_christian 2014-10-16 14:31:48 UTC
Looks similar with Bug 84819. I just knew it called "Referencing cells in a table (structured referencing)" :D

Perhaps both reports can be merged?
Comment 7 m.a.riosv 2014-10-16 16:52:36 UTC
Hi @ign_christian, sure.

As this is now as enhancement maybe it's easier resolved the other one as duplicate of this one. Or change the other and resolve this as duplicate.
In any case perhaps could be redone the title.
At your choice :)
Comment 8 ign_christian 2014-10-17 02:23:56 UTC
*** Bug 84819 has been marked as a duplicate of this bug. ***
Comment 9 Eike Rathke 2015-02-20 11:53:19 UTC
The Table feature's "structured references" need to be implemented in the formula compiler, plus the necessary table definitions to actually make them work.

See also OOXML Part 1, 18.5 Tables and following, though the actual formula syntax is *not* defined there.
Also https://support.office.com/en-US/Article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

I'm working on this.
Comment 10 Eike Rathke 2015-03-02 23:38:00 UTC
And as we know that one reference is never enough, here is another one with some more but also some less information. https://support.office.com/en-us/article/Use-structured-references-in-Excel-table-formulas-75fb07d3-826a-449c-b76f-363057e3d16f
Comment 11 Eike Rathke 2015-04-30 15:28:51 UTC
Table structured references have been implemented on current master, to be included in the 5.0 release.

Note that the structured references are not re-exported yet, but transformed to A1 style reference notation instead. The table context is lost on export.
Comment 12 m.a.riosv 2015-04-30 16:35:18 UTC
Great news Eike,
as I have not excel, I need to learn a bit about it. I hope to find time to do some test before the bug hunting session from May 22 to May 24.
Comment 13 m.a.riosv 2015-05-02 11:22:06 UTC
Created attachment 115265 [details]
Test file (csv)

I have trying to save the formulas using a csv (saving formulas option) as workaround, but with tested version calc crash opening csv files.
https://bugs.documentfoundation.org/show_bug.cgi?id=91015

On first test I have found that:

Move works like Copy, adapting the reference.
This happen with Ctrl-X or drag&drop with mouse.

When dragging Ctrl doesn't do like it does in excel for table references, I suppose it is not implemented for now.

Version: 5.0.0.0.alpha1+ (x64)
Build ID: 393c51cee8bc3de5a2a9f4e04161c59e8712f631
TinderBox: Win-x86_64@42, Branch:master, Time: 2015-05-02_01:20:07
Locale: es-ES (es_ES)

I don't know if for now it's fine report here or I must fill a new bug.
Comment 14 Mike §chinagl 2015-05-21 16:00:07 UTC
This bug fix is mentioned in the release notes of the coming LibreOffice 5.0 (see release notes https://wiki.documentfoundation.org/ReleaseNotes/5.0). Therefore it would be wonderful if this feature really worked well, otherwise it should not be mentioned in the release notes. In the notes it reads:

tdf#85063 (Eike Rathke)

    Table structured references in spreadsheet formula expressions are imported from Excel OOXML spreadsheet documents.

    Usable with defined database ranges, which OOXML tables are imported to.

    Note that the structured references are not written to saved documents yet, but transformed to A1 style reference notation instead. The table context is lost on export.
Comment 15 Eike Rathke 2015-06-02 22:31:39 UTC
(In reply to m.a.riosv from comment #13)
> Move works like Copy, adapting the reference.
> This happen with Ctrl-X or drag&drop with mouse.

Please elaborate, what do you mean? Anyway, for such editing problems (if it is one) please file a separate bug instead of cluttering up this one. Thanks.

> When dragging Ctrl doesn't do like it does in excel for table references, I
> suppose it is not implemented for now.

That is an editing feature and not directly related to the implementation of the formula part.
Comment 16 Eike Rathke 2015-06-02 22:36:56 UTC
(In reply to Mike §chinagl from comment #14)
> This bug fix is mentioned in the release notes of the coming LibreOffice 5.0
> (see release notes https://wiki.documentfoundation.org/ReleaseNotes/5.0).
> Therefore it would be wonderful if this feature really worked well,
> otherwise it should not be mentioned in the release notes.

I understand your concerns, but I'd rather announce the working parts of the feature and list the not yet implemented functionality and shortcomings than be totally silent about it. Previously such table structured references didn't work at all and calculations using them didn't deliver results other than error.

Any suggestion how the release note description should read for this case?

Or should we really totally omit mentioning it?
Comment 17 m.a.riosv 2015-06-03 22:06:55 UTC
(In reply to Eike Rathke from comment #15)
> (In reply to m.a.riosv from comment #13)
> > Move works like Copy, adapting the reference.
> > This happen with Ctrl-X or drag&drop with mouse.
> 
> Please elaborate, what do you mean? Anyway, for such editing problems (if it
> is one) please file a separate bug instead of cluttering up this one. Thanks.
> 
Submitted https://bugs.documentfoundation.org/show_bug.cgi?id=91842
Comment 18 Eike Rathke 2015-06-11 14:40:40 UTC
Added more details to the release notes.
Comment 19 Eike Rathke 2015-06-25 14:22:08 UTC
Structured references within named expressions will be supported as of 5.0.0.2 (rc2)
Comment 20 aleksander.chuikov@gmail.com 2015-06-28 20:10:29 UTC
Structured references don't work with INDIRECT()
=SUM(INDIRECT("table1[col5]")) returns #ССЫЛ! (Russian for #REF!)
Comment 21 Eike Rathke 2015-06-29 09:01:47 UTC
Thanks for the hint, but please don't fiddle with the bugzilla Version field or others.
Comment 22 Eike Rathke 2015-06-29 16:34:08 UTC
Added support for use in INDIRECT, should be available in 5.0.0.3
Comment 23 aleksander.chuikov@gmail.com 2015-07-14 13:15:29 UTC
(In reply to Eike Rathke from comment #21)
> Thanks for the hint, but please don't fiddle with the bugzilla Version field
> or others.

sorry, I am a newbie
Comment 24 aleksander.chuikov@gmail.com 2015-07-14 13:20:46 UTC
(In reply to Eike Rathke from comment #22)
> Added support for use in INDIRECT, should be available in 5.0.0.3

thank you, now it works correct
Comment 25 Eike Rathke 2015-09-11 15:40:58 UTC
Further work has been done to support Table structured references, which are now (master, to-be 5.1) also exported again, see https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references
Comment 26 Yan Pas 2015-09-20 17:10:52 UTC
According to https://wiki.documentfoundation.org/ReleaseNotes/5.1#Writer Structered refference is saved to OOXML, but will it be saved to ODF (ods)?
Comment 27 Eike Rathke 2015-09-21 09:15:04 UTC
No. The ODF OpenFormula part (aka ODFF) currently doesn't define Table structured references.
Comment 28 raal 2016-06-13 20:28:49 UTC
*** Bug 45833 has been marked as a duplicate of this bug. ***