Bug 91278

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