Bug 57204

Summary:	INDIRECT function changed but not documented in online HELP
Product:	LibreOffice	Reporter:	rh_libre
Component:	Documentation	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---	QA Contact:
Severity:	normal
Priority:	medium	CC:	quantumboy, rh_libre
Version:	3.6.3.2 release
Hardware:	All
OS:	All
Whiteboard:
i915 platform:		i915 features:
Attachments:	Pair of linked ODS with screenshots showing results under v3304 to v4132.

Description rh_libre 2012-11-16 18:38:44 UTC

The online help at
http://help.libreoffice.org/Calc/Spreadsheet_Functions#INDIRECT
 specifies the . Sheet Name separator for A1 format for example

This has now changed and made some file references in INDIRECT functions cease to work (being composed of a mixture of hard-coded text strings and text from a nearby cell on the current row concatenated using the "&")

The INDIRECT function has now switched to something different in terms of specifying paths and sheet names to different spreadsheets, which made my old spreadsheet (from version 3.4 of LibreOffice, and before that OpenOffice and originally edited from a Microsoft Excel original) cease to work:

To be clear, I'm amalgamating key figures from a number of weekly Sales spreadsheets onto a Total Sales spreadsheet.

The last row, corresponding to this week (ending 18th Nov 2012) contains INDIRECT references to various figures in the file "Sales WE20121118.ods" in the same folder as "Total Sales.ods".

The previous rows once contained a set of INDIRECT functions, but have since had the referenced values (which will never change once the week is over) hard-coded using Paste Only Numbers, the Paste Special... equivalent (or in Excel, Paste Values) to limit the hundreds of separate file accesses that would otherwise be required.

Each Monday I drag the handle to automatically generate the new INDIRECT lookups for the following week then copy and paste the values over the penultimate week to hard code them.

I'm using a date in YYYYMMDD form to specify part of the filename in cell C464, whose contents are currently the text "Sales WE20121118.ods"(without the quotes)
In cell D464, I use the function to link to the weekly sales file referred to:
=HYPERLINK("C:\Users\Public\Documents\Test\"&C464&".ods",C464&".ods")

The HYPERLINK still works as it did in v3.4, so no problem there.

The problem comes in later columns where I try to pick out specific cells in the file I HYPERLINKed to.

This form now works:

=INDIRECT("'file:///C:/Users/Public/Documents/Test/"&$C464&".ods'#$Sheet1.$Z$5")

and I've just worked out that the relative path (same directory) also works in v3.6.3.2 release:

=INDIRECT("'"&$C464&".ods'#$Sheet1.$Z$5")

In LibreOffice 3.4, which I previously used, this worked, but now produces a #REF! error:

=INDIRECT("'["&$C464&".ods]Sheet1'!$Z$5")

So it's clear that:
the square brackets [ ] around the filename must go,
but the leading single quote should remain,
a #$ must now precede the Sheet Name,
and the '! before the cell name must be replaced by .$

I appreciate that automatically detecting this and helping the user to make changes might be a problem in INDIRECT functions because the changes are within quoted text strings or sometimes text strings generated in another cell.

Nonetheless, the online HELP seems to indicate the wrong separators (or not mention that they've changed between versions) making troubleshooting somewhat difficult.

Comment 1 Owen Genat 2013-11-24 13:01:40 UTC

Created attachment 89711 [details]
Pair of linked ODS with screenshots showing results under v3304 to v4132.

There does indeed appear to be a problem with the indicated help page not being as clear as it could. The indicated help page was last updated 2012-09-14‎, which is prior to this bug being reported (2012-11-16), so the presently displayed information is evidently indicative of the problem. In fact, since the earliest version of this page (2010-12-06) no revision has ever altered the section dealing with the INDIRECT() function. The information on this page appears to be inaccurate with respect to Excel notation used to refer to filename+sheet+cell. This is the key problem statement that causes confusion:

> the Excel address in INDIRECT("filename!sheetname"&B1) is not converted into the Calc address in INDIRECT("filename.sheetname"&B1).

I am not sure why the double quotation marks are used where they are on the help page. I think they should be used around the entire expression in brackets, with single quote marks around the filename. In the Calc Guide v3.4, p.250 indicates the general format for a reference as:

='file:///Path & File Name'#$SheetName.CellName

The single quotes only ever go around the filename and not filename+sheet, so this would seem to be one area where the help page examples are wrong. This is contrary to the provided example =INDIRECT("'["&$C464&".ods]Sheet1'!$Z$5") which perhaps should instead be =INDIRECT("'["&$C464&".ods]'Sheet1!$Z$5") but this remains unclear. 

In the initial report there is also a reference to a "filename in cell C464" and then "in cell D464" a HYPERLINK() function references C464. All three INDIRECT() function examples however then refer to C464. I initially thought this should be D464, but I now understand C464 to contain "Sales_WE20121118" while D464 contains the full filename "Sales_WE20121118.ods". The original report stated that C464 contained the full filename, but this cannot be right given the extensions (.ods) used in the INDIRECT() functions.

With this in mind I think I have accurately prepared an example (Sales_WE20121118.ods and Sales_total_v3304.ods) under Ubuntu 10.04 x86_64 using v3.3.0.4 OOO330m19 Build: 6 (refer attached). In the example there are separate rows showing use of the filename without extension and with extension. In each row I have labelled the several different forms of filename+sheet+cell referencing used in the INDIRECT() function as:

1. '[f]s'!c    i.e., =INDIRECT("'[filename]sheet'!cell") erroneous test case,
               brackets and wrong placement of single quote marks
2. 'f!s'&c     i.e., =INDIRECT("'filename!sheet'&cell") help page Excel notation, 
               wrong placement of single quote marks
3. 'f'!s&c     i.e., =INDIRECT("'filename'!sheet&cell") adaptation of (2), 
               correct placement of single quote marks (but wrong)
4. 'f.s'&c     i.e., =INDIRECT("'filename.sheet'&cell") help page ODF notation, 
               wrong placement of single quote marks
5. 'f'.s&c     i.e., =INDIRECT("'filename'.sheet&cell") help page ODF notation, 
               correct placement of single quote marks (but wrong)
6. 'f#$s'.c    i.e., =INDIRECT("'filename#$sheet'.cell") Calc Guide v3.4 notation, 
               wrong placement of single quote marks
7. 'f'#$s.c    i.e., =INDIRECT("'filename'#$sheet.cell") Calc Guide v3.4 notation, 
               correct placement of single quote marks
8. 'p/f#$s'.c  i.e., =INDIRECT("'/path/to/filename#$sheet'.cell") Calc Guide v3.4 notation, 
               wrong placement of single quote marks
9. 'p/f'#$s.c  i.e., =INDIRECT("'/path/to/filename'#$sheet.cell") Calc Guide v3.4 notation, 
               correct placement of single quote marks

Basically, only (7) and (9) are expected to work, although (1) is also reported as initially working. I opened the Sales_total spreadsheet under Ubuntu 10.04 x86_64 running these LO versions:

- v3.3.0.4 OOO330m19 Build: 6
- v3.4.6.2 OOO340m1 Build: 602
- v3.5.7.2 Build ID: 3215f89-f603614-ab984f2-7348103-1225a5b
- v3.6.7.2 Build ID: e183d5b
- v4.0.6.2 Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24
- v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a

The attached screenshots indicate that cases (1), (7), and (9) do indeed display the expected result under v3.3.0.4 through v3.5.7.2. From v3.6.7.2 onward only forms (7) and (9) display as correct.

Comment 2 Owen Genat 2013-11-24 13:03:42 UTC

Per comment #1 confirmed. Status set to NEW. Platform to All/All.

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.