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.
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.
Per comment #1 confirmed. Status set to NEW. Platform to All/All.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: *Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later) https://www.libreoffice.org/download/ *If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior *If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT *Update the version field *Reply via email (please reply directly on the bug tracker) *Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-04-18
No change in the wiki.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
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.
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping-20180903
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