HI all,
I have a VERY annoying issue - I have seemingly been spoiled by the ease of using MS-Excel and Libre has me beat - I have been unable to do even the most rudimentary of sorting tasks -“SORT” data in rows & columns.
After marking the leftmost col. select ‘Sort Ascending’ I get the error as shown! There are no explicit Named Ranges / or ‘Arrays’ …
So what am I doing wrong?!! This is almost causing me to return to MS and simply cough up the €99 / year!!!
You seem to use somewhere in the range to be sorted an array formula. Search for cell formula showing something like {=...} (watch the curly brackets { and }, which indicate an array formula). You may also want to upload a sample file and to provide a detailed description which range you want to sort using which criteria. See also LibreOffice Help - Array Functions and following note therein:
The cells in a results array are automatically protected against changes. However, you can edit or copy the array formula by selecting the entire array cell range.
(Sorting a range is a change, since cell content is rearranged)
This hurts all users of LibreOffice, which are the one’s you address here. You are absolutely free to do so, but it’s hard to believe you never had any issue with MS-Excel.
-1- I wouldn’t assume Excel can sort a range containing the output of an array-formula.
-2- If you want to sort sucht results, you need to
-2a- Copy them and to Paste Special... elsewhere without the formula.
-2b- Sort them in the new location.
If your only concern is the price you should join the caravan of greed. That’s not LibreOffice.
Office software from this Free, Libre, and OpenSource branch isn’t just free of charge, but
-1- Regards a really open and stable file-format specification (what MS never did afaik).
-2- Will thereby protect sustainability of your documents against the caprices of a money harvester.
-3- Is already under some relevant aspects better than commercial offers.
-4- Will contiunue to get better instead of creating more earnings.
-5- Depends on users being aware of chances, risks, and values - not only of current prices.
Hook the cutomer first, then lock him in. This achieved you can milk him as you like. Whose concept?
Hi, thanks for the input and your thoughts … to my chagrin, I did write at my most frustrated and did not intend to insult or belittle.
However:
I used excel for many years and created multiple Applications for Helpdesk operations, among which I had to ‘Row-Sort’ tables that included simple formulas that either referred to external data, or horizontally within the row. I never came across any issues in this context.
This particular table contains similar formulas having only horizontal references and no absolute cell-addressing. Thus my issue remains.
Removing the formulas would only mean that I will have to recreate them. Since this (poorly designed) table has to be reworked and the author is not available I am not in a position to re-invent his “Wheel”.
Could somebody be kind enough to indicate what might constitute an ‘Array Formula’ - what I can see /
understand does not have these properties.
“Ordinary” formulas are moved depending on the sort criteria - what may have unwanted effects sometimes. Therefore in LibO was introduced the option Update references when sorting range of cells for Calc a few years ago. (Unfortunately it’s global to the document.)
Ranges containing formulas entered for array evaluation, and outputting to more than one cell, cannot be sorted, IMO independent of what spreadsheet software is used (except if output ranges of array formulas get assigned and maintained a property like CurrentPermutation in the background - which is very unlikely). If I am wrong, but simply too silly to understand, I would need an example file demonstrating by “before / after” how Excel did it.
And yes. I also did a lot of sorting in Calc.
BTW: To what “file format” did you last save the documents you got the problems with?
If you upload a file showing the problem for you, I will check. Probably the error mesage is misleading (even more than anyway).
Hi, I think I have found the resolution to this (with your help)
The table is currently in LibO native format (ODS) and was exported, by myself, from an XLSX WS.
I have found in one block of formulas, some {array formulas} mixed in with cells with ‘normal’ formulas and also ‘blank’ cells.
I had missed these because I only inspected the first few rows. Now I can reconstruct the original author’s thinking and rebuild the WS … Thanks for slowing me down and getting me out of PANIC mode.
The table contained inconsistent / randomly strewn formulas. Once identified, the Fix was easy.
Thanks for the time and patience of the Users ‘Lupp’ & ‘Opaque’