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
Created attachment 107883 [details] Sample Invoice file
Created attachment 107884 [details] Sample Invoice file 2
Created attachment 107885 [details] Expense Report -1 for bug report
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.
(In reply to m.a.riosv from comment #4) > Hi Zeki, thanks for reporting. hİ > 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.
Looks similar with Bug 84819. I just knew it called "Referencing cells in a table (structured referencing)" :D Perhaps both reports can be merged?
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 :)
*** Bug 84819 has been marked as a duplicate of this bug. ***
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.
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
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.
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.
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.
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.
(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.
(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?
(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
Added more details to the release notes.
Structured references within named expressions will be supported as of 5.0.0.2 (rc2)
Structured references don't work with INDIRECT() =SUM(INDIRECT("table1[col5]")) returns #ССЫЛ! (Russian for #REF!)
Thanks for the hint, but please don't fiddle with the bugzilla Version field or others.
Added support for use in INDIRECT, should be available in 5.0.0.3
(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
(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
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
According to https://wiki.documentfoundation.org/ReleaseNotes/5.1#Writer Structered refference is saved to OOXML, but will it be saved to ODF (ods)?
No. The ODF OpenFormula part (aka ODFF) currently doesn't define Table structured references.
*** Bug 45833 has been marked as a duplicate of this bug. ***
*** Bug 80956 has been marked as a duplicate of this bug. ***