Bug 57204 - INDIRECT function changed but not documented in online HELP
Summary: INDIRECT function changed but not documented in online HELP
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
3.6.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
 
Reported: 2012-11-16 18:38 UTC by rh_libre
Modified: 2018-10-09 11:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Pair of linked ODS with screenshots showing results under v3304 to v4132. (118.29 KB, application/zip)
2013-11-24 13:01 UTC, Owen Genat (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
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 (retired) 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 (retired) 2013-11-24 13:03:42 UTC
Per comment #1 confirmed. Status set to NEW. Platform to All/All.
Comment 3 QA Administrators 2015-04-19 03:22:10 UTC Comment hidden (obsolete)
Comment 4 Buovjaga 2015-06-18 11:57:08 UTC
No change in the wiki.
Comment 5 QA Administrators 2016-09-20 10:10:17 UTC Comment hidden (obsolete)
Comment 6 Olivier Hallot 2018-02-27 20:12:47 UTC
The page is

https://help.libreoffice.org/6.1/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153181

From the description in comment#2 

<quote>
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
</quote>

and current help contents, it looks the help page is correct when states that Ref parameter must be a text expression, and therefore must be treated as string, with all limitations it bears.

Please provide here the text to fix or to be inserted in the help page.
Comment 7 QA Administrators 2018-09-03 14:50:11 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2018-10-09 11:32:05 UTC
Dear Bug Submitter,

Please read this message in its entirety before proceeding.

Your bug report is being closed as INSUFFICIENTDATA due to inactivity and
a lack of information which is needed in order to accurately
reproduce and confirm the problem. We encourage you to retest
your bug against the latest release. If the issue is still
present in the latest stable release, we need the following
information (please ignore any that you've already provided):

a) Provide details of your system including your operating
   system and the latest version of LibreOffice that you have
   confirmed the bug to be present

b) Provide easy to reproduce steps – the simpler the better

c) Provide any test case(s) which will help us confirm the problem

d) Provide screenshots of the problem if you think it might help

e) Read all comments and provide any requested information

Once all of this is done, please set the bug back to UNCONFIRMED
and we will attempt to reproduce the issue. Please do not:

a) respond via email 

b) update the version field in the bug or any of the other details
   on the top section of our bug tracker

Warm Regards,
QA Team

MassPing-NeedInfo-20181009