Created attachment 115588 [details] Original.xlsx @Triager - Markus has said that I don't have to identify the exact portion of the formula that is wrong, it is sufficient just to confirm that it's not being imported correctly :) Windows 7 Version: 5.0.0.0.alpha1+ Build ID: 5b3a30f40a7ce476922649b734f6ede1c2fdef4b TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-05-13_01:52:36 Locale: en-US (en_US) 1) Open Original.xlsx Observed: Look at column F - lots of Err:508 errors, compare to original.pdf Formula: =IFERROR(INDEX(Input[],MATCH(DATEVALUE(DateVal)&DailySchedule[[#This Row],[time]],LookUpDateAndTime,0),3),"-")
Created attachment 115589 [details] Original.pdf
Error came from this part of formula: DailySchedule[[#This Row],[time]] If we have database range for "DailySchedule", I can't see a "time" column in this range. There is also another error in the DATEVALUE function: DATEVALUE(DateVal) fails because DateVal has date-text formatted as MMMM D, YYYY Eike did a great job with structered reference in master 4.5 see https://bugs.documentfoundation.org/show_bug.cgi?id=85063
Looks like an error in our formula parser. @Eike: might be one for you as it seems to be related to the new structured reference parsing.
There are two (or three) problems: 1. LookUpDateAndTime is a named expression that contains Table structured references. TableRefs in named expressions aren't supported yet. * If occurrences of LookUpDateAndTime in the formulas are replaced with it's content Input[DATE]&Input[TIME] the Err508 goes away. However, the formula then still does not work as can be seen if the IFERROR functionality is removed so the formula reads =INDEX(Input[],MATCH(DATEVALUE(DateVal)&DailySchedule[[#This Row],[time]],Input[DATE]&Input[TIME],0),3) because 2. The DailySchedule table is defined to $'Daily Schedule'.$E$4:$F$36 that does not include a header row and there is no TIME header field. Excel apparently allows such construct and remembers the column headers in the OOXML <table><tableColumns><tableColumn> elements. Importing tableColumns definitions is not implemented yet. * This can be worked around by entering TIME in E3 and defining the DailySchedule name to $'Daily Schedule'.$E$3:$F$36 3. Furthermore, Excel seems to treat the MATCH lookup array argument special, because both Input[DATE] and Input[TIME] evaluate to range references ($'Event Scheduler'.$E$5:$E$17 and $'Event Scheduler'.$F$5:$F$17) with which an implicit intersection reference is created (in LibreOffice, in other context also in Excel) depending on the position of the formula where they are used, so actually only F5:F17 could have meaningful results. * My guess is that Excel internally creates an array of the Input[DATE]&Input[TIME] expression when used as a lookup array argument of the MATCH function. This may be a general feature of the MATCH function, and maybe [HV]LOOKUP as well, and not restricted to TableRef arguments. Needs to be evaluated in Excel. All this is lots to do..
Update: 1) named expressions with TableRefs is implemented for 5.0 2) importing tables without header fields as cell content is implemented for 5.0, but such tables currently don't survive modifications of the table structure, for example inserting/deleting columns operations 3) remains to be done
Yet another quirk: 4. with DailySchedule[[#This Row],[6:00 AM]] Excel takes the result of a numeric cell content as formatted as string ("6:00 AM") as column header, which we currently don't support. Which isn't necessarily a good idea either, because representation may change depending on the locale, in this case it works because the locale is set to fixed English (USA). Something the user has to be aware of.
Hum.. or not. The original formula has DailySchedule[[#This Row],[Time]] but DailySchedule is a table without header cells, it seems we adapt the formula to the first row's content of that column then. So more work to do. However, that document relies on undefined behaviour, e.g. DateVal is defined to 'Daily Schedule'!$F$3 that contains a date in string form, formatted to ""MMMM D, YYYY"" and is used in DAY(DateVal) whereas DAY() expects a date serial number instead, or DateVal+1 is calculated. These things may or may not work, even DATEVALUE(DateVal) which is used heavily relies on that locale dependent date representation being accepted. The document probably won't work in many other locales than en-US. Bad coding ;-)
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d77947929c7f02cebe3d3e5d79c78642a8a439ba TableRef: generate error for header-less column references, tdf#91278 related It will be available in 5.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=18b5fe95d2d5b4158c5749c467298e4564537876&h=libreoffice-5-0 TableRef: generate error for header-less column references, tdf#91278 related It will be available in 5.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-0-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=12e0032fc91c9f7bc3cf8dcd13b9fbbae26890f0&h=libreoffice-5-0-0 TableRef: generate error for header-less column references, tdf#91278 related It will be available in 5.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Formulas and named expressions referring header-less tables are now (master, to-be 5.1) also imported and exported, see https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references