Bug 75617 - Support external named ranges fully
Summary: Support external named ranges fully
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.2.1 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-02-28 18:10 UTC by www.BillHowell.ca
Modified: 2014-05-29 15:06 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description www.BillHowell.ca 2014-02-28 18:10:07 UTC
I have just updated to LibreOffice Version: 4.2.1.1 Build ID: 420m0(Build:1) with Kubuntu KDE Platform Version 4.11.5.  This update seems to have resolved whole computer freeze-ups due to unintended cell moves and hopefully will resolve wifi-sleep drive whole computer freeze-up that has been happening only for ~1 month or so.

But now a new error has appeared: Some formulae are changing the case of quoted text to lowercase - a fatal problem when dealing with file names and paths.  For example : 
     =IF($N5+D$2+1<Today,-99,COUNTIF('file:///home/bill/contacts/0_ToDo.ods'#$Dates,$N5+D$2))
is changed to: 
     =IF($N5+D$2+1<Today,-99,COUNTIF('file:///home/bill/contacts/0_todo.ods'#$dates,$N5+D$2))     
This seems to depend on copying the cell, or perhaps the function used? (here COUNTIF).  A different formula, lso referring to a different workbook sheet, is not changed (at least, not yet) : 
     ='file:///home/bill/Contacts/0_ToDos.ods'#$TimeLog.E6205

I couldn't find any more "Writing" or formatting aids to turn off to prevent the case change - perhaps I missed something.  I suppose that if somehow text could be used with an "eval" or "execute" type unction (execute a text string), this could be avoided, but off-hand I can't remember such a function in Libre Calc (indirect would just move the same problem elsewhere, I think).  A fix would be to write a script to do the recalcs - but that defeats a big part of why a spreadseet is used for such a simple function.

Any suggestions?
Comment 1 m_a_riosv 2014-03-01 02:55:27 UTC
Hi Bill@BillHowell, thanks for reporting.

Reproducible with:
Win7x64Ultimate.
Version: 4.2.1.1 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b

The case happens when link is to a named range, not with and address range.
'file:///home/bill/contacts/0_ToDo.ods'#$Dates, I guess 'Dates' is a named range.

As workaround, change the named range with their address.
Comment 2 www.BillHowell.ca 2014-03-01 07:08:23 UTC
Thanks, m.a.riosv!
That solved "most" of the problem, in terms of the high percentage of cells in my sheet with formulas that now are stable with respect to a "reversion of lowercase filepaths". As an example :
     =COUNTIF('file:///home/bill/contacts/0_ToDos.ods'#$Todos.$E$5:$E$198,$N7+D$2)
works quit well, and seems to be "stable" with respect to ?lowercase degredation? with time. 
But that doesn't apply to ALL cells, as per : 
     =IF($N5+D$2+1<Today,-99,COUNTIF('file:///home/bill/contacts/0_todos.ods'#$todos.ods,$E$5:$E$198,$N5+D$2))
I wonder if the nesting of formula "COUNTIF" and others have this effect (recursion failure), as compared to the "simple, bare" expressions?
However, it's Friday night at midnight, and I have just returned from the local think tank, and no longer trust my judgement or statements.  So I'll take a more reasoned look tomorrow, hopefully with a report of total success... Time and mental regurgitation solve many problems (more than recursion, I think, and only the shadow knows what part sleep may play?).
Comment 3 www.BillHowell.ca 2014-03-01 18:01:07 UTC
All is well this morning, with the substitutions of the "spelled-out" cell ranges in place of range names in a different file/sheet.  Late last night I didn't notice the error in the remaining problem cells, as per : 
     =IF($N5+D$2+1<Today,-99,COUNTIF('file:///home/bill/contacts/0_todos.ods'#$todos.ods,$E$5:$E$198,$N5+D$2))

which of course should have been : 
     =IF($N5+D$2+1<Today,-99,COUNTIF('file:///home/bill/contacts/0_ToDos.ods'#$Todos.$E$5:$E$198,$N5+D$2))

Note that the file name now no longer changes to lowercase.

However, I suspect that this "solution" puts multi-spreadsheet environments into a dangerous position, because without the proper range names, any structual change to a sheet could mess up related formulae.  For example, by inserting rows in one sheet, formulae in another book-sheet that refer to it will point to the wrong cells (I tested this by inserting a row - and saw that the problem is real).
Comment 4 m_a_riosv 2014-03-02 01:14:56 UTC
More than a solution it is a workaround, the bug exist and the status as NEW is right.
Comment 5 www.BillHowell.ca 2014-05-29 15:06:55 UTC
Kohei Yoshida, m.a.riosv : 

I apologize for my long-delayed response, but I just structurally changed my spreadsheet, and modified formulae back to using named ranges.  The solution has worked for me, and will greatly facilitate spreadsheet re-arrangements in the future.

Thanks!