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?
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.
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?).
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).
More than a solution it is a workaround, the bug exist and the status as NEW is right.
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!