https://bugs.freedesktop.org/show_bug.cgi?id=57204
Priority: medium
Bug ID: 57204
Assignee: libreoffice-bugs@lists.freedesktop.org
Summary: INDIRECT function changed but not documented in online
HELP
Severity: normal
Classification: Unclassified
OS: Windows (All)
Reporter: rh_li...@bmharding.freeserve.co.uk
Hardware: All
Status: UNCONFIRMED
Version: 3.6.3.2 release
Component: Spreadsheet
Product: LibreOffice
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.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
