[libreoffice-users] [CALC] row_only and column_only cell references
Hello, I've been having problems with certain type of *references* in Calc. For example, the formula sum(1:2) works correctly in other spreadsheet software, but it errors in Calc. The most problematic cases are related to functions such as indirect(), address() and the like. For example, the following formula should tell whether a number in A1 is prime or not: =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT(1:INT(A1^0.5=0))=1 but Calc doesn't seem to accept the type of reference (ini_row:fin_row). Instead I have to add a column to the reference, as in: =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT(a1:aINT(A1^0.5=0))=1 to make it work in Calc. There are several settings that affect the way Calc parses and shows some type of cell references, so I wonder whether there is some setting that I should change for Calc to be able to accept this type of references (ini_row:fin_row), or whether this is some kind of bug or lack of a feature. A similar issue happens with column-only references, such as sum(A:B). To be clear, I'm not saying that row-only (or column-only) references are better in any way. I'm just trying to make them work as in (a.k.a compatible with) other spreadsheet software. If there is a better place (mailing list, irc, forum,...) to ask this question, please let me know. TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Retrieve a specific message from the ML
Hello, I am trying to retrieve a specific message from the mailing list (so to reply to it), by sending an empty message to users+ge...@global.libreoffice.org as per the help from users+h...@global.libreoffice.org . I have already sent a mail according to the specific msg number I want to retrieve, but have not received an answer. If the msg number would be, say, 12345, is users+get-12...@global.libreoffice.org the correct address? Or, am I doing something wrong? TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Retrieve a specific message from the ML
Am 13.10.2013 20:24, schrieb Ady: Hello, I am trying to retrieve a specific message from the mailing list (so to reply to it), by sending an empty message to users+ge...@global.libreoffice.org as per the help from users+h...@global.libreoffice.org . I have already sent a mail according to the specific msg number I want to retrieve, but have not received an answer. If the msg number would be, say, 12345, is users+get-12...@global.libreoffice.org the correct address? Or, am I doing something wrong? Looks fine. I just tried it with your mail and it worked (but took rather long, about 5 minutes) Are you sure you are subscribed to the list? Are you sending from your subscription address? Did you choose an appropriate N ? E.g. for your mail, to which I'm actually responding, it's the 34301 - the number from the Return-path header line. Nino Thank you for the info. Well, apparently, finding the correct msg number is part of the problem. Let's assume I sent the wrong number, I should be able to receive at least the msg that corresponds to *that* number (since I am already subscribed to the list). I waited for much more than 5 minutes without receiving *any* reply to my request (sent to users+get...), independently of the number I used. But the more important issue regarding this matter is to be able to find out the correct number without having the original message (the one that I'm trying to retrieve). Evidently, I won't be able to read the Return-path value before I have the msg. I thought that the msg number would correspond with the number presented in the mail archives (34065 would correspond to my first email in this thread, according to the mail archives, which is of course different from 34301 from the aforementioned Return-path). So, let's start from the beginning, with the relevant info that is not included in the help of this ML: How a user can obtain the correct msg number so to use it in users+ge...@global.libreoffice.org ? I would expect for the answer to that question to be somehow related to the public mailing list archive, but it seems I am wrong. So, anyone knows the correct answer / method? TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] [CALC] row_only and column_only cell references
On 10/12/2013 07:55 AM, Ady wrote: Hello, I've been having problems with certain type of *references* in Calc. For example, the formula sum(1:2) works correctly in other spreadsheet software, but it errors in Calc. I was not aware that you could do that in any product. It is not supported in Calc of which I am aware. Villeroy has a messy solution (see post 6). Well, you probably should read all the posts... http://forum.openoffice.org/en/forum/viewtopic.php?f=9t=9527 So, the ony soltions I have seen based on a quick search is to either define a range, or to use offset. The most problematic cases are related to functions such as indirect(), address() and the like. For example, the following formula should tell whether a number in A1 is prime or not: =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT(1:INT(A1^0.5=0))=1 but Calc doesn't seem to accept the type of reference (ini_row:fin_row). Instead I have to add a column to the reference, as in: =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT(a1:aINT(A1^0.5=0))=1 to make it work in Calc. There are several settings that affect the way Calc parses and shows some type of cell references, so I wonder whether there is some setting that I should change for Calc to be able to accept this type of references (ini_row:fin_row), or whether this is some kind of bug or lack of a feature. A similar issue happens with column-only references, such as sum(A:B). To be clear, I'm not saying that row-only (or column-only) references are better in any way. I'm just trying to make them work as in (a.k.a compatible with) other spreadsheet software. If there is a better place (mailing list, irc, forum,...) to ask this question, please let me know. TIA, Ady. -- Andrew Pitonyak Quoting from CG4107-FormulasAndFunctions.pdf : Calc can not reference a whole column of unspecified length using A:A or a whole row using 1:1 which you might be familiar with in other spreadsheet programs. This piece of info was not so easy to find. Since other spreadsheet software (in fact, more than one) indeed supports A:A: and 1:1 references, I thought that either Calc would support it too, or at least would be easier to find that it doesn't. For example, using Calc to open some xls file with such type of references would end up with errors. The xls file itself would open, but there would be no hint about the reason for the #ref or #name errors (among the possible errors that this type of cell reference would end up with). Regarding a workaround, once the user finds out that the problem is in the type of reference (which is not always evident), then by adding some column (or some row) to the reference would probably avoid the error. Other methods (e.g offset, range names) would be more close to the original range (complete row / column). This workaround is of course not always available, specially when receiving a spreadsheet file from someone else (originally prepared and saved with other programs). I am still wondering if some setting in Calc would (or should) allow for this type of cell reference. For example, by using Excel A1 instead of Calc A1 in Calc - Tools - Options - LibreOffice Calc - Formula - Formula syntax. There are many different settings that might influence the accepted behavior, and I really don't know / understand the effect of each one (I didn't find any explanation of what each setting really means), so instead of playing with each possible combination, I decided to ask here in the ML (and not all of those settings are located in the same place). If this type of cell reference is _completely_ unsupported, perhaps it should be mentioned in the help files (?). I would agree that referencing complete columns or complete rows should be rarely used, and discouraged, but perhaps supporting a formula that already uses it (specially when using or receiving files originally saved with other file formats) is not such a crazy idea. So, is there any combination of settings that would help in this type of cases? [off-topic] BTW, if I may... Having separated mailing lists according to the program (Writer / Calc / Impress / ...) might be more adequate for users, specially since other methods of communication with LibreOffice / TDF are not really effectively working. [/off-topic] TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] [CALC] row_only and column_only cell references
Hi :) Is it possible to change the way Calc handles it's formulas by doing Tools - Options - Calc - Formula and then use the drop-down to make them use MS Excel ways. I can't The problem with this setting is that there is no explanation of what it really means / implies (other than =$Sheet2.C4 vs. =Sheet2!C4). The difference between R1C1 and A1 is likely clear enough for anyone that might want / need to use it. But what are the differences between Calc A1 and Excel A1? Are row_only references accepted when setting it to Excel A1? Even if I try and test the possible difference regarding 1:1 and A:A references, I don't know what else could change. Without documentation, I can't know of pros and cons, or compatibility or any other implications of this particular setting. help thinking that trying to copy MS Excel at a time when they have finally admitted, and corrected, some very fundamental errors in their formulas. Regards from Tom :) FWIW, I should point out that Excel is no the only one accepting this type of reference. I'm not saying that Calc should promote bad practices or anything like it. I'm not saying that using row_only references is better in any way. I am not promoting its use. But when receiving a file from other sources and it errors, it is not always easy to track down the problem or the incompatibility. And even then, it is not always possible for one user to edit and share the file with others. Additionally, if a user of Calc searches for some method or formula to achieve something he doesn't know how to do it by himself, it is common to find such solution expressed in the context of other spreadsheet software (typically, Excel). From a final user's perspective, I don't see a reason not to support it. Of course there might be technical / development reasons, but this is a *users* mailing list :). So, I left with several questions here. Hopefully, someone has some answer(s) or can point into the right direction? TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Retrieve a specific message from the ML
On 13/10/2013, Ady ady...@hotmail.com wrote: Hello, I am trying to retrieve a specific message from the mailing list (so to reply to it), by sending an empty message to users+ge...@global.libreoffice.org as per the help from users+h...@global.libreoffice.org . I have already sent a mail according to the specific msg number I want to retrieve, but have not received an answer. If the msg number would be, say, 12345, is users+get-12...@global.libreoffice.org Assuming your are referring to digest mode, all is correct. Sometimes the delay to receive the retrieved message is quite long ( 10 minutes at a guess). No, I'm not referring to digest mode. I am referring to nomail mode, and then retrieving specific messages by using users+ge...@global.libreoffice.org. The problem: there is no way to know the desired correct N for someone subscribed by users+subscribe-nom...@global.libreoffice.org so to be able to retrieve it. There are several ways to _read_ messages. There is no effective way to _retrieve_ a specific message (since the correct N is unknown to a nomail subscribed user). To the Admin(s) of the ML, any answer to this question? TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] LO 4.1.2 Hebrew parentheses bug
I just updated to LO 4.1.2 (English US) on Intel Mac OS X 10.7.5 and the parentheses mapping for Hebrew text seems to be broken. When I switch the input language to Hebrew, the parentheses are reversed when I use the Times New Roman font... However, some Hebrew fonts appear to be working properly. Could this be a continuation of this problem: http://ask.libreoffice.org/en/question/18912/bidirectional-text-and-clo sing-bracket-bug/ The most logical way to map the parenthesis is that the button 9 should give a left parenthesis and the button 0 should give a right parenthesis, regardless of OS input language, document language, or text direction. This is the way Hebrew behaves in other Mac applications such as TextEdit and Mail... Please advise whether this bug is able to be fixed, and if there is anything I can do on my install to fix it. Thank you in advance for your time and attention. Best regards, Gabriel Kaufman Here is my current experience with LO 4.1.3.1 under Windows, using a physical US keyboard layout (so left-parenthesis is physically located at shift+9 and right-parenthesis is located at shift+zero): 1_ Visual or Logical CTL settings don't affect the displayed result. 2_ With the alignment set to left-to-right (ctrl+shit+A in LO Writer): 2.1_ keyboard layout set to US-English, OK 2.2_ keyboard layout set to HE-English, OK 2.3_ keyboard layout set to HE-HE, parenthesis inverted: pressing shift+9 shows the parenthesis that is seen over the zero in the physical US keyboard, and vice versa. 3_ With the alignment set to right-to-left (ctrl+shit+D in LO Writer): 3.1_ keyboard layout set to US-English, parenthesis inverted. 3.2_ keyboard layout set to HE-English, parenthesis inverted. 3.3_ keyboard layout set to HE-HE, OK. I am not sure which behavior should be exactly the adequate one, specially when I see that changing the CTL settings between Visual and logical doesn't seem to affect the result. In any case, this seems to be a bug and should be reported. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc Function Wizard - IF Result differences
Hi, Entering a formula into the Function Wizard in Calc shows up two different answers in the 'Result' and 'Function Result' answer box. If the editing cursor is placed in a certain section of the formula, only the 'Function Result' answer box holds the correct answer i.e. 751.52004. Pressing 'OK' puts the 'Result' answer box value in the calculated cell, which unfortunately is the wrong answer i.e. 751.25. I have looked at the help file to see if there was a difference between the Result answer boxes but found none. The initial formula is: =IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)) In addition I have tried changing the formula without success to try and reduce the multiple nested IF functions: i.e.: =sum((IF(0G4=$G$20,G4*$F$20,0),(IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),0),(IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)),0) The SUMIF function was also tried but my brain just gave up. I cannot see how the VLOOKUP function will help either as I am not looking up values from cells, I am calculating a single value in H4 based in a variable input in G4. Have a peak at the uploaded spreadsheet on Nabble: http://nabble.documentfoundation.org/file/n4077843/Electricity.ods You will see that provided you enter a value for G4 below 600 the correct answer is shown i.e. for 600 in G4 H4=750. The moment G4 exceeds 600, even by 1, there is a problem. There must be an answer and or my formula syntax is wrong unbeknown to me. Help would be very much appreciated Sincerely Hylton -- The spreadsheet structure you are using is not so adequate for this calculation. I have a suggestion for your formula in H4. But, before I post it, you should know that there are better possibilities, and that my suggestion doesn't solve all your problems in that spreadsheet. Additionally, the formula I am suggesting might not be optimized, and it is certainly not the only solution. Anyway, in your posted spreadsheet, Electricity.ods - Data2 sheet - Cell H4, I suggest the following formula as one possibility: =(0G4)*(G4=$G$20)*(G4*$F$20)+($G$20G4)*(G4=$G$20+$J$20)*($G$20*$F$ 20+(G4-$G$20)*$I$20)+($G$20+$J$20G4)*(G4=$G$20+$J$20+$M$20)*($G$20*$ F$20+$J$20*$I$20+(G4-($G$20+$J$20))*$L$20)+($G$20+$J$20+$M$20G4)*($G$ 20*$F$20+$J$20*$I$20+$M$20*$L$20+(G4-($G$20+$J$20+$M$20))*$O$20 Hopefully, the email width format won't mess up my intention (a reply to this email probably will). Best Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc - avoiding circular references
I don't remember whether there is a simple keyboard shortcut to simultaneously insert the same value/formula in multiple cells in Calc. I'd like to clarify the above paragraph. One thing would be to insert the same value/formula simultaneously in multiple cells. A more powerful keyboard shortcut would be to: 1_ Select a range of cells; 2_ Type in a formula; 3_ Press a combination of keys that would: 3.1_ insert the typed in value/formula in the first cell of the selected range, and then; 3.2_ would extend, or fill in, or copy+paste the first cell to the rest of the cells in the selected range. If the typed in formula would be =A1+1 (without quotation marks), then: A_ the same formula would mean the _exact_ same formula, as if it would be copying the content of the cell (as oppose to copying the first cell; or B_ copying the first formula while respecting relative references too (e.g. =A2+1 ; =A3+1; ... ) Whether case B would also copy the entire first cell (including formatting) into the rest of the selected range would be something to consider if/when such shortcut gets to be considered for development in Calc. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] [CALC] Detective Trace inadequate display
If this is replicable by other users, IMHO there should be at least some way for the user to easily note that the traces should be refreshed (because they are not really pointing to/from the adequate intended cells). Well, no. This is a bug and it should be fixed, not worked around. I agree, it should be fixed. That's why I added Better yet, this displacement should not even happen. Please file a bug (take a look here: https://wiki.documentfoundation.org/QA/BugReport) and post link to report here at the list. Bug report #70886 opened: https://bugs.freedesktop.org/show_bug.cgi?id=70886 I will confirm and bibisect it, so developers can jump straight to fix (if they notice...). OTOH, this arrow is not much usable anyway (it just shows that value is from other sheet), so I suppose it might not get highest priority. I disagree. The Detective feature is already very crippled in LibreOffice. This inadequate behavior makes it even harder and generates more confusion, specially when using complex spreadsheets (which is when users most need the feature). If anything, IMHO the Detective feature should be very much improved (not left with inadequate behaviors because it is already crippled anyway). Thank you and Best Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] [CALC] Detective Trace inadequate display
Dnia 2013-10-26, o godz. 12:42:10 Ady ady...@hotmail.com napisal(a): Bug report #70886 opened: https://bugs.freedesktop.org/show_bug.cgi?id=70886 Confirmed, added bibisect log. It's now up to developers to pick up challenge and fix that issue. I disagree. The Detective feature is already very crippled in LibreOffice. This inadequate behavior makes it even harder and generates more confusion, specially when using complex spreadsheets (which is when users most need the feature). If anything, IMHO the Detective feature should be very much improved (not left with inadequate behaviors because it is already crippled anyway). I never said that this bug SHOULD be left open and not fixed in reasonable time. I only explained why some people might skip over this one and devote their time to other issues, which they see as higher priority. As of improving detective - well, it could use some improvements, like almost any other part of suite. In software development, there is always something to do. In free software, usually there are very limited resources (manpower, infrastructure, money, time etc.). That means we end up with more work than we can complete. And that means that should is not really helpful, because it does not bring us any closer to out goals. The question is not what should be done?, but who may do that?. -- Best regards Miroslaw Zalewski Thank you all for your replies, and special thanks to Miroslaw for his bibisect and follow up at https://bugs.freedesktop.org/show_bug.cgi?id=70886 . FWIW, I'd like to clarify my prior comments. 1_ If / when this issue gets the developers' attention, I'd like for them to find a *real* solution (and even improvements), not just a workaround. 2_ If a solution can't be achieved for some technical reason (other than availability of developers' time), I suggested a possible alternative behavior: instead of leaving the inadequate arrows displayed as normal, change their color until they are displayed adequately again. This suggestion was meant not as my preferred desired method to solve/workaround the issue, but only as a trigger to alternative thoughts (whether for this particular issue or for potential future improvements to the Detective trace features), just in case. 3_ When I said that the (IMO) insufficient Detective features should not be used as a reason not to bother with this issue either, my intention was to explain why, IMO, that's not the best potential reasoning, for whoever would read this email topic (specially for developers that might be tempted to think that way). 4_ Of course I understand that this issue might never get resolved just because developers might think this matter deserves low priority. My answer is that, at some point, the Detective feature should be greatly improved, not just as my subjective desire, but as a high priority for Calc users in practical objective terms. Of course developers might choose to invest their time in whatever they want; no complains. For users, there are other spreadsheet tools capable of performing just fine with simple sheets and/or simple calculations. In my experience, the Detective (trace) feature is crucial for users with either complex formulas, multiple sheets and/or many different relations between many cells. IMO, if Calc is to be used for something more than just relatively simple cases, then in practical terms the Detective feature *must* be improved (a.k.a., not such a low priority). I am aware that elevating the development priority of the Detective feature means lowering it for some other task. I am just expressing my desire and opinion (and my reasoning as to why this bug should be solved), _independently_ (not against nor in opposition) of what has been expressed in prior emails / posts in this topic. I am hoping this objective reasoning will also trigger some developer(s) to act on it. Again, thank you all. Best Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Open odt file in Internet Explorer
Hi, I'm in a Citrix environment. When a user clicks an odt link file in IE9 he gets an error message saying that the option -Embedded was not found. The file is stored on a local drive. However when it's a .docx file Swriter.exe opens correctly. One other strange thing if I change the file type association by typing .ODT=LibreOffice.Docx the documents opens fine but we don't want to change the association as we don't know if there could be other issues. Thanks. https://help.libreoffice.org/Common/ActiveX_Control_to_Display_Documen ts_in_Internet_Explorer -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] font drop-down menu list issue.
Well, I stated in a different thread that I had an issue with 4.1.2 with the font drop-down list of fonts to change some highlighted text to. The 4.1.2 was for Calc and a 4 page sheet. Well, I now have had it go wacko with 4.0.5. Though I had a 44 page Writer document that I wanted to change the default font to a fixed width one. What happens is as I scroll down the font list with either the mouse on the scroll bar or using the down arrow, I get down to the 10th or 12th font name and the list pops back to the starting position. Now, if I go to the Format Character and choose the font from that dialog box, I have no trouble, except that fact that I cannot see what the font looks like in the list, like you see in the toolbar that has the font list in it. SO, in the 4.1.2 Calc, it happened with a select all for about 4 printed pages, but it did not with 4.0.5 Writer for 4 or 5 pages of a text document. But when I needed to do a select all for the 44 page document and change the font, it had the problem. I know that I have used the select all with a 40 or 50 page document in the past with earlier versions, but this now seems to be an issue with the 4.0.5 and 4.1.2 versions. I have not yet updated to 4.0.6 or tested 4.1.3 to see if the problem is still there. So it is now happening with Ubuntu 12.04 64-bit LO ver 4.0.5 [Writer], and 4.1.2 [Calc]. Of course I have a lot of fonts installed in the .font folder. But I have had a similar number +/- 30 or 40 fonts for the past 2-3 years. I have also seen this behavior, in recent versions, including in LO 4.1.3.2 on Windows. How to replicate: 1_ Open Calc. 2_ While only A1 is selected, change the Font to something different than the default. 3_ Click on the all cells area (above row 1, to the left of column A) so to select all cells of the current sheet. Note that the Font name (in the toolbar) is now empty, since the current selection includes more than one Font. 4_ Select the Font field (in the toolbar). Try to (almost) select a new different font by inserting the initial character of the name of the new desired font (and wait; do not insert new characters and do not press [ENTER]). After about 1 second, the inserted letter (corresponding to the name of the Font I want to set) disappears, leaving again the Font field empty. When the text or selected cells all use the same Font, I probably won't see the flickering in the list. When the text or selected cells use more than one Font, the probability to see the flickering is higher. The flickering seems to be easier to see when I perform the Font selection slower rather than faster (e.g. insert only the first letter of the font name and wait; the field will be empty again after approximately one second). Hopefully this procedure helps to replicate the issue. Thanks and Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] CALC convert text to numbers
I have a spreadsheet that is the output of a SQLite Database on Android. It contains numbers that have been formatted as text. When I copy the columns into my LibreOffice Spreadsheet I want to make sure that they are interpreted as numbers. However they come in as text and the manuals and help at Libre Office are less than useful. https://help.libreoffice.org/Calc/Converting_Text_to_Numbers implies that this conversion happens automatically but it sure doesn't I found reference to a value function, but no information on how to apply it to this data. I tried just changing the format of the cells to be number but that didn't do anything at all. I've also attempted various styles of paste special but still it doesn't work. There has got to be a way to quickly say this text is all really numbers and get it working! Please help If the numbers (formatted as text) are already saved in your spreadsheet, I would normally suggest a simple procedure involving paste special and multiply (by 1). This has worked for me in several other spreadsheet tools. But Calc will (currently?) fail, because Calc adds a single quotation mark at the beginning of the cell. So what seems to be just 1 (without the double quotation marks), in Calc actually is '1 (without the double quotation marks, but including the single initial single quotation mark). If you currently don't see the initial single quotation mark, you could see it (in the formula bar) by copying one of these cells and pasting it in a new one (among other options). This single quotation mark will even remain after changing the format from 'text' to 'numbers', so this is what makes the solution less than simple. You could select the relevant cells, change their format and then 'find and replace' on that same selection. But, since this is a special (hidden) character, I'm not sure how to make it happen ('find and replace' might not find the specific character). As a simple user, I see this hidden addition of the initial single quotation mark as a _BUG_, and as one of those basic features that work poorly in LibreOffice Calc than in several other spreadsheet tools. I don't know if this behavior can be corrected or improved. Now, if your data is not yet imported into Calc, you can change the type of data from standard or text to numbers during the 'import' procedure. This task is simple enough if the numbers are already located under the same column in the csv / text file that you use as source to import the data into Calc. All the above comments are relevant only if you don't need to do the conversion in a repeatedly, scripted / batch / automatic way. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] CALC convert text to numbers
If you are importing a txt or csv file into Calc you can use INSERT SHEET FROM FILE. The first part of the wizard will you can click OK. The second dialog has a section Other Options. In this section check Detect Special Numbers. There is a preview screen which shows what the data will look like. If the column has the same data type (numbers, text, datetime) Calc will convert the raw text into a more appropriate data type. Also, once checkbox is selected, Calc will remember the setting for subsequent imports. This may be easier than cut/paste special. -- Yes, as I said, if there is still a need to 'import', that's simple enough. But if the data is already in a spreadsheet, there is a well-known paste special simple procedure. It works in other (more than one) spreadsheet tools and usually is fast enough. The more important matter is that LO Calc is making this simple conversion more complicated than necessary. I wonder if this should be reported as a bug / enhancement, so the behavior is changed / improved. Any comment from Calc developers would be really appreciated. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] CALC convert text to numbers
As a simple user, I see this hidden addition of the initial single quotation mark as a _BUG_, and as one of those basic features that work poorly in LibreOffice Calc than in several other spreadsheet tools. I don't know if this behavior can be corrected or improved. As far as I understand it, the hidden initial quotation mark is what marks the contents of the cell as text. This is the same in MS Excel, IIRC. So basically, this isn't a bug, but intended behaviour, to give you a way to specify that a number should be interpreted as text and not as a number. For example, if you enter 0283, the leading zero will always be stripped, because it is interpreted as a number and the leading zero is superfluous, but if you enter '0283, then this means you have entered a text string and the leading zero is kept. The format of the cell doesn't change this behavior, it only changes the *display* of the contents, not the interpretation of the contents. At least, as I understand it. Paul Well, yes, but no :). In Excel and other spreadsheet tools, you could: 1_ In an auxiliary non-formatted cell, insert the number 1. 2_ Copy that auxiliary cell. 3_ Select the cells with numbers that are currently formatted as 'text' that you want to convert. 4_ Paste special (all), multiply. 5_ Delete the auxiliary cell. If the desired format is not just a 'general' number, then you can format the auxiliary cell before copying it. This simple procedure cannot be used in Calc, just because this hidden single quotation mark. This procedure is successful in other spreadsheet tools that use the single quotation mark too. So, why not in Calc? BTW, the quotation mark is not necessary for all cells containing numbers formatted as text, but that's off-topic here. My point is still relevant: the aforementioned procedure should be plausible in Calc, just as it is in other spreadsheet tools. There are workarounds, like using the 'VALUE' function in an auxiliary column and then copy+paste back; yet I still wonder about this hidden (annoying) single quotation mark. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] CALC convert text to numbers
Why not set a cell to the formula =right(len(CELL)-1) which returns the all the characters except for the first. -- No, that won't work. You probably meant: =RIGHT(A1,LEN(A1)-1) where A1 is the 'text' cell. But that one will fail too, because the initial single quotation mark is not really part of the content of the cell. (Note: it might work in some particular situation, but it is not a generic method.) The 'VALUE' method is a valid workaround, but it might be a heavy method, depending on the amount of data. I would prefer the 'paste special'+multiply by 1 method to work. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] CALC convert text to numbers
Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui I could ask you the same type of question regarding paste special + multiply by 1 method. A user might not know of the extension, or about any extension for that matter. A user might not use extensions, ever. Why would anyone need to search, install and use an extension, when other spreadsheet tools can apply a well-known multiply by 1 method? In other words, if very simple spreadsheet tools (not just Excel) can use a very simple and well-known method, perhaps instead of thinking of how to incorporate the CT2N extension into the core in Calc, the same time could be invested to make Calc work as expected with paste special? To be clear, I'm not complaining, just posting my thoughts. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Engaging users: initial results of the survey
Hello, As there were some exchanges about the survey here and as I advertised it on this mailing list as well, I thought you might be interested by my initial analysis: http://standardsandfreedom.net/index.php/2013/11/10/users-the-final-frontier/ Thank you for your participation! -- If I may... There are many ways for users to communicate: LibreOffice forum, Ask LibreOffice, several LibreOffice mailing lists, Nabble, wiki, Bugzilla, and several irc channels. The problem is, IMHO, they are sometimes too many and too complicated. Let me explain with a simple example. In the release notes for LO 4.1.3, it said that the release was bit by bit the same as RC3. Well, that was incorrect, as it is the same as 4.1.3.2, a.k.a. RC2 (there was no 4.1.3.3). What a casual reader needs to do if he happens to catch the typo? Can he easily report the one-character mistake? Does anyone think that this typo deserves opening a new bug report in Bugzilla? For each contact method mentioned above (each ML, Nabble, wiki, Bugzilla, forum,...), a user needs to go through an additional sign up, sometimes requiring multiple steps. In our example (RC3 typo), do you think a casual reader would go through a sign up process just to report one wrong character? Just as an example, I am subscribed to the users ML, and I found annoying to go through additional sign-ups for Nabble. I can understand that there might be relevant reasons for this; but it is still annoying :). On the other hand, if a user is interested in Writer only, having to receive emails regarding Draw (or anything else than Writer) is one reason not to subscribe to the users ML. So perhaps separated per-program lists should be available, instead of one unified users ML? (I am not necessarily recommending it; just mentioning such potential situation.) Then we have several irc channels, but none of those channels targeted to users are really active, ever (e.g. #libreoffice and/or #libreoffice-qa). So what's the point of publishing the existence of those irc channels if they are not really open with someone from the LibreOffice Team being present in the channel? I'm not saying answers should be on real time. For irc to be relevant for users, someone at least should maintain the channel open and saving logs, checking it once a day or so. This is one contact method that could be easily used to report the typo mentioned in our example. One day is one typo, another day is another typo. Then there is some minor low-priority bug in the installer (e.g adding a link to the desktop even when the user unchecked the corresponding box during the installation process). Then the wiki might need some little correction or update... For each minor issue, a user could just think not worth going through all the sign up troubles for each different service. As a consequence, none of those little corrections are reported / performed. What's the point of Ask LibreOffice if each question is seen, say, 3 times in a one week period? Most questions are unanswered. Similarly with LibreOffice forum. A user might not bother to sign up to such a method that is hardly ever used by relevant users; and if it goes through it anyway and no answer is provided (as it is the case with most Ask LibreOffice topics), it would probably generate a rejection response towards LibreOffice. If a user signs up and opens a bug report, that's because it is significant for him. Is this procedure relevant if the bug report is left unanswered for 2 years? Is this user going to keep reporting additional bugs? Evidently, solving bugs requires man power, so finding a simpler method to report you have a st*pid typo might help reduce wasted time, for both developers and users. So, making the contact methods more relevant, easier (unified?) sign up procedures and actually maintaining active and relevant the different contact channels would contribute to receive more feedback and eventually reduce wasted time. I am writing not to complain, but to voice my personal view of some of the ways to improve user's involvement in LibreOffice. I admit I am not sure if any of these changes would be the most effective use of man-power, so I'm not going to call these recommendations. These might be potential considerations for potential improvements. Whether they are _effective_ use of man-power, I don't really know. Thank you and Best Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: CALC convert text to numbers
On Mon, 11 Nov 2013 20:54:54 -0600 Denis Navas Vega denis.na...@gmail.com wrote: Paul, Make this test. 1. Open a new worksheet. Format some cells, say from A5:A10 as text. Write some numbers on those cells. Ok 2. Copy those cells to other column, say to C5:C10 and format as number (format @). The '@' format is text, so I'm not sure what you want me to do, format as text, or format as number? You will see that the cells now shows an ' before the numbers. Yes, if I format as number, this is correct. 3. Write a formula in other cell, multiplying with 1, for instance, E5=C5*1 Ok Now check the cell and you will discover that you have a number. True enough, when I copy these cells, and paste special, pasting only text and numbers, not formulas or all, then I get numbers in the cells. Therefore, those numbers with ', that in reallity are text, can be multiplied by 1, to transform it to a number. As Brain explained to me, an implicit VALUE() must be done on the text when multiplying by 1. As a side note, I was not able to use the method of copy -- paste multiplying by one. How do you mean? When is the multiply done? If I copy the numbers from the cells with the formulas it works fine. So now I'm confused, if this *does* work, why was Ady complaining about it not working? -- Well, the procedure I mentioned involves copying one cell only, which means it is a faster method, as oppose to having to copy perhaps a lot of cells (e.g. a whole column; or having to add many new VALUE formulas; or having to add a new column and multiply by 1 each cell and then paste special...). I repeat the procedure that I posted before (which works correctly in other spreadsheet tools): 1_ In an auxiliary non-formatted cell, insert the number 1. 2_ Copy that auxiliary cell. 3_ Select the cells with numbers that are currently formatted as 'text' that you want to convert. 4_ Paste special (all), multiply. 5_ Delete the auxiliary cell. If the desired format is not just a 'general' number, you could optionally format the auxiliary cell before copying it. Although the Tools - Text to Columns... method in Calc is nice, the procedure I am describing is more flexible. Unfortunately, it currently doesn't work in LibreOffice Calc 4.1.3.2. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: CALC convert text to numbers
Although the Tools - Text to Columns... method in Calc is nice, the procedure I am describing is more flexible. I'm not sure why you say it is more flexible. Text to columns would be the correct way of doing this, and I can't right now think of a case where you would need your way. Can you give an example? -- First, evidently I meant to Data - Text to Columns... (not Tools...). My apologies. Currently in LO Calc 4.1.3.2, the Text to Column method will let you convert the text into one of a few specific number formats. If you want to convert to other formats (e.g. percentage, scientific, or some accounting type, or...), then paste special, multiply by 1 *should* let you do it. Also, if the 'text' cells contain additional characters such as thousand delimiters and the like, it might be possible to reduce the necessary steps to obtain the desired format conversion. But since this well-known method is currently unavailable in LO Calc, all this is wishful thinking only. I could give more examples, and of course that you could get the same result by using several steps. On one hand, this method is effective, efficient and well-known in several other spreadsheet programs, and for users that already know it, it seems at least strange that LO Calc doesn't support it. (BTW, I still think something fishy is happening with the single quotation mark in LO Calc.) On the other hand, there are (less efficient) alternatives to eventually get to the same final result in LO Calc. Seeing the current ratio of bug reports vs. bugs resolved, where in many of them there is no alternative available so to get the desired result using LO Calc, I am slightly reticent to actually report this as a bug (or as a potential enhancement, whichever the adequate term would be for this case). When other issues regarding paste (and/or paste special) get to be resolved, perhaps then this issue will get improved too. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: CALC convert text to numbers
These solutions are far too complex especially if you have very large sheets. Why not use the extension CT2N? It is simple, very straightforward and you can decide ,whole sheet, or just parts. The ' is just removed. I understand it is to be included as standard, in which case there will be no need to install the extension. You can find it here: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates We are going in circles. The extension not always works (see bug reports), and the supposedly too complex solutions you are referring to take a few seconds and _less_ than 10 clicks. Regarding including the extension as standard, I hope the devs are using their valuable time to take care of features that _really_ have _no alternative_ in Calc. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: CALC convert text to numbers
Hi Ady, On Thu, 14 Nov 2013 15:45:33 +0200 Ady ady...@hotmail.com wrote: On one hand, this method is effective, efficient and well-known in several other spreadsheet programs, and for users that already know it, it seems at least strange that LO Calc doesn't support it. I'm still not sure it is correct behaviour, but you do have a strong argument. There is no correct behavior for this. The possibility to interpret / parse the content of a cell as a number (e.g. for usage in other cells) even when the original cell is formatted and displayed as 'text' is a feature, which is already present in other spreadsheet programs. A theoretical requirement to have to *always* use VALUE for any and all usage of those 'text' cells would trigger a natural enhancement request to make it easier. You can use VALUE if you think it is needed for some situation, but experience says that there are many simple cases where imposing its usage would negate common sense. There is a reason why such common feature has been present in spreadsheet programs for decades now. Perhaps thinking about it this way might help: 1_ In an auxiliary 'number' cell, insert the number 1; [ENTER]. 2_ Select that auxiliary cell. 3_ Copy that auxiliary cell. 4_ Select the cells with numbers that are currently formatted as 'text' that you want to convert. 5_ Paste special (all), multiply; OK. This paste special step is performing the following actions: 5.1_ It pastes first the cell format from the auxiliary cell, converting the selected cells from 'text' format to a new 'number' format; and, 5.2_ It multiplies the content of the selected cells by the content of the auxiliary cell (by 1 in this case); and, 5.3_ It adds all other characteristics of the auxiliary cell to the selected cells (e.g. comments). 6_ Delete the auxiliary cell. To be clear, the 'text' format of the cell shouldn't block the multiplication (in the above procedure or when using its content in other cells), since the *content* of the cell should be independent of the way it is being displayed in the cell. You should be able to use that same content in whichever way you want, whether you display the cell with leading zeroes, decimal places, as text, or in yellow. I don't know if LO Calc actually performs these actions in this way and order. I am just trying to explain why this feature makes sense (as it does in other spreadsheet tools). (BTW, I still think something fishy is happening with the single quotation mark in LO Calc.) The single quote is a red herring. It is correct behaviour, and the same as other spreadsheet software. It is the implicit conversion of the text to a value that differs. We are going in circles. The example that Denis Navas Vega already gave (with steps that you already followed) shows that the initial quotation mark is not necessary when you format the cell as 'text' before inserting its content. We all agree that the initial single quotation mark should not be part of the cell content; it's just an optional formatting aid to be used when fits the need. Yet, when in LO Calc you manually convert a cell (that you first formatted as 'text' and then inserted a pure number, without ') from 'text' to 'number' (with ctrl+1, number, general), the *previously nonexistent* initial quotation mark is *kept* (or rather *added*); it shouldn't!!! (a.k.a. BUG) That's the difference between LO Calc and other spreadsheet tools where the multiply by 1 conversion works as expected. On the other hand, there are (less efficient) alternatives to eventually get to the same final result in LO Calc. And perhaps more correct ones. Correct in the sense that I'm not sure implicit conversions should be done on values. But it is a convenient shortcut... See my prior rant about non-existent correct behavior vs common sense feature for this case. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: [Calc] Relative references behavior for worksheets
Having sheets named Sheet1, Sheet2, Sheet3 etc. any reference from Sheet2 to Sheet1 or from Sheet3 to Sheet2 refers to the _previous_ sheet. When you copy this reference to the first sheet, then there is no previous sheet which is why you get #REF!.X1 Any absolute reference to $Sheet1 refers to the first sheet. Since there is always a first sheet, this reference can be copied anywhere. When you copy an absolute reference to $Sheet3 (3rd sheet) into a document with only 1 or 2 sheets you get #REF!.X1 because there is no third sheet. You are explaining the difference between absolute and relative references to worksheets. I already know how it works. I am interested in users replicating the test I presented and commenting on whether they would rather see a different default behavior in Calc (as it happens in other spreadsheet programs). Let me express the matter with different words. Currently, the default behavior would give: Sheet2.A1: =Sheet1.A1 What I am saying is that the _default_ behavior is problematic, inconvenient and unexpected by users. Instead, the _default_ behavior should give: Sheet2.A1: =$Sheet1.A1 Meaning, the _default_ behavior for cells should remain, using relative references, but the _default_ behavior for sheets should be modified, to use absolute references (note the $ in front of the sheet name, and no $ for the column nor for the row of the cell). By changing the _default_ behavior: _ users would get the traditional (expected) behavior; _ there would be less confusion among users (sometimes seeing #REF! errors, and sometimes without understanding why their worksheets are failing); _ in case relative references to sheets are actually needed by a user, the correction is easier from absolute to relative notation than the other way around. I hope I am explaining my point clearer now. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] [Calc] Relative references behavior for worksheets
Hello, I had the intention to file a bug report for Calc, but then I thought that maybe I should first ask for some user's feedback. I would like to know if the behavior I am about to describe can be replicated with several versions of LibreOffice and/or under different configurations / scenarios / OSes. Other comments are also welcome, of course. The following is the text of the bug report I was about to file in. Apologies for its length; I have seen too many of these problems already and I want to explain the problem (and its solution) as clear as possible. TIA, Ady. 1_ New Calc spreadsheet (aka workbook). 2_ For this test, we need at least 2 Sheets (Sheet1 and Sheet2) in the new spreadsheet document. 3_ In Sheet2 - A1 : =Sheet1.A1 4_ Right click on the Sheet2 tab and select Move/Copy Sheet 5_ In the Move/Copy Sheet dialog, select Copy, then OK. 6_ Select the new Sheet2_2 , A1 cell. 7_ Note that the content of Sheet2_2 , A1 is =#REF!.A1, and the result being displayed is #REF!. 8_ Right click again on the Sheet2 tab and select Move/Copy Sheet... (as in step #4 above). 9_ In the Move/Copy Sheet dialog, select Copy, select move to end position, then OK. 10_ Select the new Sheet2_3 , A1 cell. 11_ Note that the content of Sheet2_3 , A1 is (this time), =Sheet2.A1. 12_ Note that Sheet2 has been copied to Sheet2_3 with _relative_ references for _sheets_; e.g. Sheet2_3 , A1 is not an exact copy of Sheet2 , A1, as Sheet2_3 , A1 contains =Sheet2.A1 (pasted with relative notation for the _sheet_), instead of containing Sheet1.A1 (as it would had been expected in other spreadsheet programs). This test shows that the Sheets are being treated with _relative_ (reference) notation by default, as cells are. There seem to be similar reports about named range of cells, and with copies to another spreadsheet file (instead of copying inside the same file, as my test here does). Additionally, changing the name of the sheets, from SheetN to something else, doesn't change this behavior. Although I understand the potential advantage in some cases, this concept and behavior of relative references to Sheets is inadequate. We have relative notation in/for _cells_ because there is a certain standard for their references, a series. But this is not true for Sheets, nor for a named range of cells for that matter. At first sight, and based on the above test, someone could potentially propose to only allow copying sheets to the end; but such idea would be wrong too. In my sample test, I only used one formula, retrieving data from one sheet from the left and then copying the sheet to the left (in the typical LTR display). But I could have multiple sheets and I could be retrieving data from surrounding sheets from both sides. The relative notation in cells works everywhere in almost all cases. Applying the same concept and *default* behavior to sheets and named ranges of cells is inadequate. I consider this to be almost a bug, and I am certainly not the only one. Using the relative notation concept (and behavior) on anything other than common cells should *not* be the default behavior, and such possibility should be optionally and explicitly selected by the user when performing each copy+paste action, or by means of the adequate notation. In other words, please leave the relative notation as default for cells only, and as optional selectable possibility for sheets and for named ranges of cells. The default notation for sheets should be *absolute references*. In fact, the only references that should default to relative notation should be references to cells, and any other references should default to absolute notation. Copying a Sheet already containing a formula pointing to another Sheet should not generate #REF! errors. Sheets' references should be inserted as _absolute_ notation by default. The current default behavior is UNexpected by users (since other Spreadsheet programs do not behave in the same way) and it can easily generate loss of data (especially in complex multi-sheet workbooks). A different wording for this situation / proposal would be: when building a formula pointing to cells from other sheets, the *default* behavior in Calc should be that the sheet's reference should use _absolute_ notation by default while keeping the default notation for cells in _relative_ form. This is the default behavior I see in other spreadsheet programs. The user should *not* have to manually convert each reference to absolute notation for sheets while keeping the relative notation for cells. Please keep in mind that, after copy+pasting a sheet, correcting all the resulting #REF! cells can be a complex task (and prone to errors), whereas modifying references for sheets from absolute to relative notation is much easier. TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists
Re: [libreoffice-users] Re: [Calc] Relative references behavior for worksheets
Ah! My response was based on entering the whole formula through the keyboard. That's my modus operandi (sp?). You're talking about using the mouse to generate references. I'll back away as I have nothing useful to say about your method. -- Jim Apologies for not being clear enough. Indeed, I am talking about the default behavior when not explicitly typing in the formula. This includes clicking on sheets, cells, and even external documents, and it includes wizards / assistance. Some users are used to type-in the whole formula, or edit by hand. Some users are used to formula wizards / assistance and mouse (click, drag, select, fill...). For some users, this is especially relevant when using complex long formulas involving multiple sheets, multiple ranges of cells, or multiple cells, as it reduces the chances of typos. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: [Calc] Relative references behavior for worksheets
I don't know about Andreas, but when I read your original steps: 3_ In Sheet2 - A1 : =Sheet1.A1 I thought you meant type =Sheet1.A1 in cell A1 of Sheet2, and expected that to behave as an absolute reference (which would be inconsistent with row and column references). From the above description, about default behaviour giving =$Sheet1.A1, it sounds like you're talking about the behaviour when clicking on a cell as a shortcut to creating a reference? Correct, I was referring to clicking on other sheets, and their cells. I have just sent another email to make it more clear. Apologies for the confusion. When typing a formula directly, it's reasonable to expect the user to be responsible for getting the syntax right, including using $ to create absolute references where necessary. It may be more reasonable to expect that references generated by clicking in cells be tailored to the more common need - whatever that may be. I can imagine working out what's most common isn't be easy though, and will probably never please everyone ;o) Indeed, explicitly typing a formula should be respected. If users explicitly type-in $A$1, or A$1, or $A1, Calc respects it (i.e. it does not change it to relative references). If users explicitly type-in $Sheet1.A1, or Sheet.$A$1, or whatever, then Calc should definitely respect the input. I think we all agree that this is correct, adequate and expected. Personally, I don't often copy entire sheets, and when I do I probably do usually want absolute references to other sheets as you suggest. On the other hand, I can also see the use of relative sheet references, e.g. for things where there's a sheet for each month with totals carried over from one month to the next. I couldn't really say which is the more common use overall, and therefore which should be the default. Another consideration is that changing the current behaviour may confuse those who are used to the way it works at the moment and find it convenient. Certainly having relative references to sheets is useful. I am not saying it is not. My issue is about the _default_ behavior. I could understand the potential inconvenience with current users of Calc, perhaps already expecting the unusual (as of other spreadsheet programs) behavior. To be clear, I am not expecting from Calc to change current formulas, or any content. I would expect to change the default behavior for new editions. If a user edits an old Calc document, what was done before is done, but IMHO in the same document the new behavior for new editions / new formulas should be as I am suggesting (and as other programs already do / behave). Moreover, considering the lack of features in Calc for multiple-sheets documents, my guess would be that such change in the default behavior would be more than welcome by experienced users. But, I could understand such change could be considered somewhat controversial by someone. So, as an alternative, I would suggest introducing an option, so the user could select which kind of references should be used by _default_ for sheets: either relative (as Calc behaves currently), or absolute ones (as any other program I know). Similar options regarding the _default_ behavior could also be added for cells' columns and for cells' rows, or for cells. By changing the _default_ behavior: _ users would get the traditional (expected) behavior; Unless it's changed recently, it seems traditional for LibreOffice (and therefore probably expected for at least some of its users) is to create relative sheet references. I meant traditional as in spreadsheet programs that are being used for more than 2 decades. Traditional for old users of spreadsheet programs. I guess you are correct about traditional being different when talking about Calc (which is in fact the conflict I am talking about). I am focusing on users and practical functionality, more than in the program (Calc). As of the more common / frequent use of relative or absolute references for sheets, we probably cannot really know. And yet, since it is easier to correct (in case the user really needs such correction) from absolute to relative references to sheets than the other way around, the suggested change in default behavior makes at least some sense. There is a reason why this issue keeps coming back to forums and alike, and even reported as if the behavior would be an actual bug (IMO, it is an inadequate default behavior, but it is not a complete bug, but close to it :). Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: [Calc] Relative references behavior for worksheets
Replicating your steps, I get a copy of Sheet2 at position #1 and any relative reference to the previous sheet needs to raise a #REF! error because you told me to reference a cell on the previous sheet. The error is perfectly clear, wanted and informative. There is nothing wrong with errors. Errors are not a slap in your face. They are not errors because you are stupid. I can not see any error in the application. Everything works as expected. If you reference something something irreferenciable you get a #REF! error. I seem to have a problem explaining the matter. I do understand how it works. I understand the error. I understand why it is giving this error. I am not complaining about receiving an error. I'll try to explain my point in a different way. Let's assume the following procedure: 1_ Click on A2. 2_ Type in an equal sign, =. 3_ Click on A1. 4_ Press [Enter] 5_ Click on A2. The resulting formula is: A2: =A1 The default behavior, as seen in this simple procedure, is that cells are referenced with relative notation. If the resulting formula would had been (by default and with no additional steps/help): A2: =$A$1 we would had concluded that the default behavior was absolute references. But we know this is not the default behavior, and users take advantage of this. All spreadsheet programs that I know of behave in this same way. To receive a formula with absolute references in Calc we would need some extra step(s) (e.g. [Shift-F4]). Now let's repeat the procedure, with a slight difference: 1_ Click on Sheet2.A2. 2_ Type in an equal sign, =. 3_ Click on Sheet1. 4_ Click on Sheet1.A1 5_ Press [Enter] 6_ Click on Sheet2. 7_ Click on Sheet2.A2. The resulting formula is: Sheet2.A2: =Sheet1.A1 Here, once again, the cell references are, by default, relative. Since we now involved multiple sheets in the formula, the resulting formula includes the sheet(s) as part of the reference. And we also see that, by default, the reference to Sheet1 is also a relative one. Here is where Calc goes differently than other spreadsheet programs, and it is here where users (that are used to other spreadsheet programs) are having problems (and even reporting this behavior as a bug, multiple times already, since they don't understand why it is failing, considering that they are used to a different default behavior). When using other spreadsheet programs, the resulting formula for the last procedure would had been: absolute reference for 'Sheet1' and relative reference for its 'A1' cell. Or, using Calc's notation: Sheet2.A2: =$Sheet1.A1 (note the $). What I am trying to convey is that Calc should change the default behavior for referencing sheets, so to behave as other spreadsheet programs do. I am not saying that: Sheet2.A2: =Sheet1.A1 is wrong, or that I don't understand the #REF! error, or that I don't understand why the error is being generated after the copy+paste procedure I described in my initial email. I am saying that the _default_ behavior should be to obtain absolute references to sheets (while keeping relative references to their cells). I am not suggesting to change the meaning of the $ in front of the sheet. I am not suggesting to change the behavior of the REF! error nor its meaning. I _am_ suggesting that, by default, the sheets in Calc should be using the $ in front of them. If a user wants to use relative notation for sheets, then such result should had been obtained by adding some step (e.g. explicitly deleting the $ in front of the sheet reference), instead of obtaining a relative reference to the sheet by default, as it is now. By changing the default behavior regarding default references to sheets, Calc would be simply imitating what other worksheet programs already do, and less REF! situation would be encountered by users. Additionally, it is easier to replace absolute references to sheets with relative references to sheets, whereas it can be very difficult to find and correct every REF! in complex workbooks. It is the *default* behavior of references to sheets that I am talking about. Hopefully I am making it more clear now. Now, if my experience with other spreadsheet programs (as I described it here) is different than other users here in the list, I would like to know about it. If the tests / steps I have presented in this email thread cannot be replicated by others, or if the default behavior seen by others is different than what I am seeing, I would really appreciate receiving feedback about it, because it would mean that I could change the default behavior in my own setup, without waiting for developers to do anything. Thank you in advance, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global
[libreoffice-users] Measure speed of formulas in Calc
Hi. For several days I´ve been searching for some method to measure performance / speed of formulas (or a range, or a whole sheet, or a complete workbook) for using it in Calc. I know there is such a thing for excel (I can provide links, if that would be acceptable), but it fails in Calc (at least for me, using version 7.4.3 on Windows 10). I´m really hoping there is such a thing already. With such tool / extension / BASIC / whatever code, we users could try different alternative formulas when a spreadsheet is getting "slow". If there is no way to measure the performance / speed of (ranges of) formulas for usage in Calc, perhaps a developer might be willing to review some of the methods / code that has been posted for years for excel and check what makes it fail in Calc, or perhaps develop something new for Calc. TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Limits in Calc
See this faq: https://wiki.documentfoundation.org/Faq/Calc/022 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Add word to the dictionnary/corrector
See: https://wiki.documentfoundation.org/Development/Dictionaries On Thu, Mar 9, 2023 at 11:52 AM Scriptance wrote: > > Hi > > I'm trying to add words to the global dictionnary and the spell checker but I > can only add to my own version of LibreOffice despite I try to add them to > all the versions of LibreOffice. > How can I do that please? > > Regards > Scriptance > > -- > Envoyé via https://mailfence.com > Email privé et sécurisé > -- Sent with https://mailfence.com Secure and private email > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Hi Regina, > But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example. Besides the typo, for that to work it would need to be all in the same worksheet, which is contrary to the initial setup. ISTM that using SUM() and COUNT() would be simpler, if the data is already with the layout as described, in multiple sheets. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Two separate issues. A_ The curly brackets are not to be introduced manually. They are only a sign that the formula is not "normal" (as introduced by pressing [ENTER]) but instead is an array formula, introduced by simultaneously pressing [CTRL]+[SHIFT]+[ENTER] (CSE). Search the wiki or the web for info. B_ Not every function will accept 3D arguments (i.e. ranges from different worksheets), even when using array formulas. You can use ABS() as an array formula, but within the same worksheet. Currently, ABS() does not accept 3D arguments, IIUC. I could be wrong. So, you would have to use ABS() in each sheet (as normal or as CSE), and then you can use AVERAGE() as a 3D formula, whether with [ENTER] alone or with CSE, depending on the specific case. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
> > ISTM that using SUM() and COUNT() would be simpler, if the data is > > already with the layout as described, in multiple sheets. > Don't see how just using SUM() and COUNT() would work. Assuming the use of > ABS was wanting to change negative values to positive values a simple sum of > numbers would give a value different from the sum of absolute values. I apologize. I wasn't clear – I didn't have enough time in order to be specific when I wrote that email, and that sentence I wrote was most probably adding to the confusion, unintentionally. I don't want to add more confusion, so let me (try to) be succinct and avoid unneeded extras. As Steve presented the case and the specific layout... _ ABS() needs to calculate each value on its own. The only way to use ABS() on a range, as far as I know, is to use it as array formula and only within the same worksheet, and there is no need (nor advantage) in this case. _ AVERAGE() can be used with multiple worksheets and in 3D references. There is no need for array formulas in this case either, and it would probably be counterproductive. _ To be clear, just in case, 3D references is not the same as array formulas. _ There are alternative ways to resolve this, but *with the layout as presented*, Steve's initial assumption was correct. ABS() expects one cell (or value) as argument, while AVERAGE() can be used as intended. My apologies again for adding to the confusion. Best regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice 7.3.7.2
In Writer, go to menu Tools > Options > Language Settings > Writing Aids. Is the dictionary of the specific language listed and checked "on"? In the same Language Settings dialog, are the relevant options checked "on"? "How to install extra dictionaries?" https://wiki.documentfoundation.org/Faq/General/101 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
If by "sheets by position" you mean things like "the next one, whichever the next worksheet would be", you can. I don't know whether you actually need this feature – please allow me to doubt it – but there is such a thing in LibreOffice Calc. Beware: if you need compatibility with other spreadsheet software, I wouldn't recommend using this feature. Not every spreadsheet software (and/or file format) supports this feature. Moreover, before investing too much time on this, you should test this with a simple basic case, save it, close the software and reopen the test file again, in order to check whether everything works as expected. You have been warned! In LibreOffice Calc, worksheets can be addressed by relative references. For cells, you add a "$" symbol for columns, rows, or both for absolute references. This is quite common, in every spreadsheet software and file format. For worksheets in LO Calc, when you precede a worksheet name with the same "$" symbol you make its reference absolute. Absolute worksheet references are the default (and only) mode for most spreadsheet tools and file formats (so they don't use any additional symbol). In Calc – I cannot emphasize that point enough – if you use the name without the preceding "$" symbol, its address is relative. Please "play" with this feature before using it in important work. For instance, use relative references for multiple (i.e. more than 2) worksheets, save the file, then move (all) worksheets around and see the resulting formulas and actual calculation results. Copy worksheets. Delete worksheets. Before and after each action, please pay attention to the resulting changes, in order to understand the effect of relative references. You should have clear knowledge of how relative and absolute references affect the results of actions such as move, copy and delete, before attempting to use them. Should I add "Use this feature at your own risk"? You have been warned, again. Don't blame anyone else! Using relative references for worksheets should not be used by inexperienced users, and it should only be used for specific purposes, not as a norm. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Libre Office version 7.4.5.1 (x64)
I would suggest "installing" the portable package, version 7.4.3. You close LibreOffice, download the aforementioned portable package to some folder and "install" it in some easy directory/folder. Please note that the "install" directory must be different than the folder where you downloaded the installation executable. Then you navigate to the location of the "LibreOfficePortable.exe" file and execute it. Test it and compare the behavior with your current fully-installed LO 7.4.5.1. Beware: you should rather avoid running them both simultaneously (i.e. close one before executing the other; avoid using any of them as the default program to open your office files). If the portable version works better, then keep it until a newer version gets out (and get rid of the other one). If it doesn't help, you just delete the folder where you installed the portable version. In either case, please report your experience, so we can all benefit from it. Good luck! -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] [libreoffice] Tool Bar Changed
> Under View .. I dont those options. > Which menu are you under? You are probably not looking at the menu but at the toolbar. The _menu_ named "View" has the User Interface options to choose from. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Possible formatting but in calc.
On Fri, Feb 10, 2023 at 1:47 PM Johnny Rosenberg wrote: > > should be reported as a bug. > I think it should. Then it's up to the developers to decide whether or not > to do something about it. Maybe it could be set to low priority, though. I agree. I already opened a new bug report for it. I linked also to this email thread, but I have not set it to new nor to low priority; I'll leave that for devs. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Possible formatting but in calc.
If there is at least one space character _after_ the AND function, then each edition of the formula adds one space character _before_ AND. After several editions, you end up with the same one space character after AND, but several space characters before it. This behavior happens even when the first comparison does not exist: =IF(AND ( To be clear, it is enough to have one space character after AND in order to trigger the additional space characters before it, each time the formula is edited. You do not need an initial space character before AND for this to happen. Without the space character after AND, the behavior is not replicated. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Possible formatting but in calc.
> If there is at least one space character _after_ the AND function, > then each edition of the formula adds one space character _before_ > AND. > > After several editions, you end up with the same one space character > after AND, but several space characters before it. > > This behavior happens even when the first comparison does not exist: > > =IF(AND ( > > To be clear, it is enough to have one space character after AND in > order to trigger the additional space characters before it, each time > the formula is edited. You do not need an initial space character > before AND for this to happen. > > Without the space character after AND, the behavior is not replicated. While the correct / expected syntax of the AND() function is not what Steve used, the question is whether the "additional spacing" behavior should be reported as a bug. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreCalc error found, need help
> That is, why only sequential ones? In order to "cut" (e.g. [CTRL]+[X]) ranges, Calc only accepts continuous areas, at least at the moment. So, cutting multiple adjacent rows (e.g. rows 5 and 6) is possible, but multiple non-adjacent rows cannot be cut simultaneously; at least not with current versions. Now, if you want to _delete_ the contents of those non-adjacent rows, or the rows themselves, then those actions are indeed possible. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Problem with using mouse scroll wheel on Mac
I guess that: https://bugs.documentfoundation.org/show_bug.cgi?id=155266 describes something of that sort (ATM set as fixed and verified). I would suggest trying with LO 7.5.9, or 7.6.4, or with the newest pre-release. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Problem with using mouse scroll wheel on Mac
> I would suggest trying with LO 7.5.9, or 7.6.4, or with the newest > pre-release. Although you already tried with the first two of those versions, I posted those versions as described by the bug report. YMMV. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Autocorrect not working
Hi Karen, Please try with: LibreOffice Writer > menu Tools > AutoCorrect > AutoCorrect Options > Replace (tab name). Use the two relevant fields to add your "shortcut" ("---") and what should be replaced with. In the same dialogue you can also review what other "shortcuts" are already there. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Libre Office bugs (Windows 10 pro PC)
> 1> If I turn on "high contrast" mode on my PC While this email won't solve your problems, I'd like to point out that MS Windows 10 has 2 (seemingly independent, but not really) settings: 1. Personalization > Colors > ** Light/Dark for Windows ** Light/Dark for the applications 2. On the same "Personalization > Colors" window, on the right side you should see the link to High Contrast configuration. Within High Contrast, there are several "Themes" available. LibreOffice might react differently to each combination of all the possible aforementioned settings. YMMV. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Can't open new version of LO
> when I click on the new version icon > on my desktop, the icon highlights but the program never opens. > I am running Windows 7 In Windows 7, open your Start Menu and look for the LibreOffice 7.6 group. Try starting LO from there, instead of using the desktop icon. If you are using an antivirus, the initial first scan might take some time. If nothing works, go to the LibreOffice official site and download a version that you think might work. If you previously tried 7.6.6.3 and that seemingly failed, try downloading 7.6.5 and install it, replacing your prior version. Please share your results in the users mailing list. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] I am searching Libreoffice portable in Czech language
For MS Windows, at: <https://www.libreoffice.org/download/portable-versions/> look for the link(s) to "Portable MultilingualAll". Usually there are 2 links (Fresh and Still) for "All" (languages) and 2 links (Fresh and Still) for "Standard" (languages). HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Changing the color of the selected cell in calc
> >> In newer versions of LO Calc, ... > > ... The relevant options is: > > menu View > "Column/Row Highlighting" > > Would "newer versions" mean something newer than 7.6.5.2? (I don't see that > option.) It was added for the LO 24.2 release. In the Release Notes for LO 24.2 you can see a screenshot of how it looks. You can also assign some customized keyboard shortcut to toggle it. ATM I don’t recall whether the color is configurable (maybe in the advance/experimental settings(?)). Have a nice day, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Changing the color of the selected cell in calc
> > make it easier to see which sell is selected? > > In newer versions of LO Calc, you can go to menu View > "Hidden > Row/Column Indicator" I apologize. That is a different setting that has nothing to do with your question. The relevant options is: menu View > "Column/Row Highlighting" My apologies again for the previous incorrect info. Have a nice day, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Changing the color of the selected cell in calc
> make it easier to see which sell is selected? In newer versions of LO Calc, you can go to menu View > "Hidden Row/Column Indicator". This setting highlights the complete Row and the complete Column of the cell that has the focus (aka active cell). As for the _selected_ cells, when I actually select a range of cells I already see them highlighted, so I am not sure what else would you need, or what might be different in your system. HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Help for Libreoffice portable
> I would like to get help for LibreOffice > portable in Czech. Is it somewhere to download it, please? Best regards For the (Windows) portable package, the offline help is available in English only. In the LibreOffice download site, you could select to download a Help package to _install_, but I am not sure how it would work with your _portable_ version. I guess you really need to know what you are doing in order to mix these 2 packages. Generally speaking, I wouldn't recommend it. Since you are using the portable package, I guess that the simplest way to read the help content in Czech would be to read it on-line: For LO 7.6: <https://help.libreoffice.org/7.6/cs/text/shared/05/new_help.html> For LO 24.2: <https://help.libreoffice.org/24.2/cs/text/shared/05/new_help.html> For the "latest" version (a moving target): <https://help.libreoffice.org/latest/cs/text/shared/05/new_help.html> HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Libreoffice Calc
> For some reason my questions are not getting posted. Hopefully the fourth > attempt works. There are at least 2 emails from you in the public users mailing list; see the mailing list public archives. I am replying to one of them. > Software: LO, Version 7.3.7.2, Linux (Mint) > > I thought that LO Calc would let one start typing c, o, l and get coliflour. > Is there some way to do this,or is it a feature request? The feature is called "AutoInput". In Calc, you can set it ON/OFF in menu Tools > AutoInput. In Calc, if you already have a value in one cell and you want to repeat the same value in another cell in the same column – it works better if the range has no empty blank cells in-between, but that's not a strict condition – then usually the behavior is as you described. If you have "coliflour" (without quotation marks) in cell A1, and something else in cell A2, then in cell A3 you could start typing "c" (without quotation marks), then a prior (partial) value would be suggested, instead of having to type-in the complete value each time. As of current versions, the feature works similar to a bash-like (auto)completion, but incidentally the feature was modified several times between LO 7.1 and 7.3, so if you are having problems with this I would suggest trying a newer version that you currently report (7.3.7.2). See: <https://help.libreoffice.org/latest/en-US/text/scalc/guide/auto_off.html?DbPAR=CALC> <https://help.libreoffice.org/latest/en-US/text/scalc/01/0613.html?=CALC> HTH, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] OS hangs while visiting a LO wiki page
On Wed, Sep 16, 2015 at 10:53 PM, <libreoffice-ml.mbou...@spamgourmet.com> wrote: > Simos Xenitellis wrote: >> >> (obviously we are off-topic) >> >> On Wed, Sep 16, 2015 at 8:46 PM, <libreoffice-ml.mbou...@spamgourmet.com> >> wrote: >> >>> Tom Williams wrote: >>> >>>> On 09/15/2015 01:43 PM, libreoffice-ml.mbou...@spamgourmet.com wrote: >>>> >>>>> Ady Ady wrote: >>>>> >>>>>> Hello Users of LibreOffice, >>>>>> >>>>>> Once in a while I visit the Release Plan wiki page, >>>>>> https://wiki.documentfoundation.org/ReleasePlan >>>>>> >>>>>>From that wiki page, I recently tried to visit the "5.0.2" -> "RC1" >>>>>> page (more than once), but my OS hangs when I click on the relevant >>>>>> link. >>>>>> >>>>>> Of course, it could be just a coincidence. I am reluctant to try to >>>>>> reach these pages again, especially considering that every new wiki >>>>>> page in the series is based on the prior one. >>>>>> >>>>>> I am using Windows Vista Home Basic 32-bit, with Internet Explorer 9 >>>>>> and Mozilla Firefox 40.0.3, all updated. >>>>>> >>>>>> The strange behavior happened with both web browsers. >>>>>> >>>>>> I don't know whether some security tool being in use (e.g. Avast) >>>>>> might be relevant. >>>>>> >>>>>> I wanted to share this unusual experience, in case someone else >>>>>> happens to see it too (or something similar, or at least uncommon), >>>>>> and/or maybe it might be worth someone checking the source of these >>>>>> wiki pages (and their related nested templates). >>>>>> >>>>>> I am aware that this behavior (hanging OS) would be considered >>>>>> "unlikely to be triggered by some wiki page". The fact that it >>>>>> happened to me more than once while following the same links (but >>>>>> nowhere else) pushed me to at least comment on it. >>>>>> >>>>> >>>>> Interesting... I don't see a problem with that page at the moment. >>>>> However, since mid July I have been experiencing occasional OS hangs >>>>> which seem to be triggered by visiting certain web pages or sometimes >>>>> scrolling down the page slightly even after it's been loaded for a >>>>> while. Revisiting the same page doesn't necessarily trigger another >>>>> hang, but some pages seem to be more prone to it than others. >>>>> >>>>> I use Windows Vista Business 32-bit, Mozilla SeaMonkey 2.33.1 (based >>>>> on Firefox 36), Avast antivirus. >>>>> >>>>> One site (www.discount-electrical.co.uk) seemed to be particularly >>>>> troublesome. At one point I couldn't even add a few items to the >>>>> shopping cart and check out before the OS hung. Hangs seemed to be >>>>> less frequent using Chrome instead, but I did still get a few. I >>>>> didn't try IE. >>>>> >>>>> I haven't had so much trouble since mid August, but then again I >>>>> haven't been visiting the sites which seemed to be triggering the >>>>> problem so much lately either. Still get a couple of unexplained hangs >>>>> a week though. >>>>> >>>>> I can't be certain that visiting the web sites is/was the trigger, or >>>>> whether that's just coincidental. As you say, it does seem unlikely! >>>>> If anything, I'd suspect some interaction with a Windows update or >>>>> perhaps Flash or Avast, since I'd just got back from holiday and >>>>> installed a load of updates just before the problems started. Another >>>>> possibility for me is overheating or failing hardware (this is a 9 >>>>> year old laptop so getting on a bit!) but I haven't got around to >>>>> investigating further. >>>>> >>>>> Mark. >>>>> >>>>> >>>>> So, when the problem happens for you, what do you do to recover? How >>>>> do >>>> >>>> you get the system working again? Have you checked the Windows Event >>>> Viewer for log messages? There m
[libreoffice-users] OS hangs while visiting a LO wiki page
Hello Users of LibreOffice, Once in a while I visit the Release Plan wiki page, https://wiki.documentfoundation.org/ReleasePlan From that wiki page, I recently tried to visit the "5.0.2" -> "RC1" page (more than once), but my OS hangs when I click on the relevant link. Of course, it could be just a coincidence. I am reluctant to try to reach these pages again, especially considering that every new wiki page in the series is based on the prior one. I am using Windows Vista Home Basic 32-bit, with Internet Explorer 9 and Mozilla Firefox 40.0.3, all updated. The strange behavior happened with both web browsers. I don't know whether some security tool being in use (e.g. Avast) might be relevant. I wanted to share this unusual experience, in case someone else happens to see it too (or something similar, or at least uncommon), and/or maybe it might be worth someone checking the source of these wiki pages (and their related nested templates). I am aware that this behavior (hanging OS) would be considered "unlikely to be triggered by some wiki page". The fact that it happened to me more than once while following the same links (but nowhere else) pushed me to at least comment on it. TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] welcome screen keyboard shortcuts strange behavior
When opening LibreOffice 5.0.6.3 under Windows ("soffice.exe"), there are shortcut keys that are supposed to perform a certain task, such as creating a new Writer Document. The relevant keys are shown as underlined (e.g. the letter "D" for a new Writer Document). The same shortcuts are also available in older versions. When I was using the older 4.4.x branch, these shortcuts were working correctly; after opening LibreOffice ("soffice.exe") I was able to launch Writer by simply pressing "d". After updating to 5.0.6.3, this same effect (opening Writer) is _not_ being triggered by the same action (pressing "d" on the keyboard). Now, after opening LibreOffice 5.0.6.3 ("soffice.exe") if I open Writer (by other means, instead of using the keyboard shortcut) at least once and then close Writer again (thus, going back to the initial LibreOffice screen), then the keyboard shortcuts start to work correctly; i.e. pressing "d" will open Writer. These shortcut keys will continue to work correctly, until I close LibreOffice completely. When opening LibreOffice 5.0.6.3 ("soffice.exe") again, the shortcuts will not work unless I open one of the components (such as Writer) at least once, and then close the component, as I described before. Searching the web, I tried to use an adequate set of terms that would lead me to some information about the matter, but I failed. 1_ Is this a known issue? 2_ Can it be replicated by other users? 3_ Is it present under other OS? 4_ Is there some new option that might have changed the prior behavior? Perhaps the specific key's combinations have changed in some way? 5_ Is it still present in newer versions (such as the 5.1.x branch)? 6_ Is there a bug report already opened? TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: welcome screen keyboard shortcuts strange behavior
On Sat, May 14, 2016 at 10:38 AM, Tom Davies <tomc...@gmail.com> wrote: > Hi :) > > Thanks for seeking feedback from here before posting a > bug-report/feature-request. As you know, sometimes a change in behaviour is > required to consolidate inconsistent behaviour or to smooth out other > coding, or to keep-up with current trends in other, similar(ish) programs or > happens somewhat unintentionally. Usually any such changes are noted in the > change-log, neatly re-written at; > https://www.libreoffice.org/download/release-notes/ > > LibreOffice/OpenOffice is over 10 years old and a lot has changed in that > time. Hopefully the newer ways are easy to adapt to and are an improvement. > Where that is not the case it's fairly easy to post a bug-report although > carefully phrasing it as a feature-request seems to have more chance of > attracting interest. > > So, please give it a fair go but feel free to post a > bug-report/feature-request now that you've narrowed it down to being a > change-of-focus issue. > > Good luck and regards from > Tom :) > I am unsure whether I should open a bug report / enhancement request, considering that I have not tested 5.1.x. From Stuart's reply, I understood that some things have been changing in the Startup Screen, and that they are still changing. I would guess that having a request for enhancement without actually reporting the behavior of the most current / updated release (5.1.x) would not attract enough attention and would probably be considered an incomplete report. Additionally, the only version from the 5.0.x branch I have tested is 5.0.6.3, so the (initial) version number in which the behavior changed would not be accurate (enough). If I cannot test the current (5.1.x) behavior (because I am not ready to update yet), how would I report a bug / request an enhancement? I mean, I could, but it would probably be mostly ignored, wouldn't it? I think it might be useful, before actually opening a request for enhancement, if someone could try to replicate the behavior / tests in the 5.1.x branch and report it here. Also, testing under other OSes might be relevant too. With more details and tests from different users under diverse circumstances / versions, a request for enhancement might be more relevant and taken more seriously. Finally, I am still unsure whether this is really a request for enhancement or rather a bug report with a regression between 4.4.x and 5.0.x, considering that the expected behavior was working correctly in 4.4.x, it is (partially) broken in 5.0.6.3 (and maybe in all the 5.0.x branch), and I see no advantage from the users' perspective to not have these shortcuts working from the start. Comments about this matter would be very welcome. I hope users can attempt these simple tests and report their results, including the OS and version information, so a potential bug report / request for enhancement would be actually worth. TIA, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: welcome screen keyboard shortcuts strange behavior
On Fri, May 13, 2016 at 7:48 PM, V Stuart Foote > @Ady, * > > I just did a clean /a admin install of LO 5.0.6.3 x64 on Windows 8.1 > > While in the StartCenter -- icon bar on left or in the thumbnail preview > pane-- with this new user profile the .UI accelerators for the StartCenter > (+o, r, e, d, s, p, r, m, a, l, and x) all function as intended. > > There has been a change now implemented for the 5.0 releases (not sure > which commit) where the key toggles from the StartCenter to the Main > menu bar. The key now has the toggle focus action that F10 alone > previously had. > > Even so, the main Menu accelerators (+f, t, and h) still open their > assigned menus. > > So, really do not see any issue here. > > And of course this is mute, as 5.0 is entering EOL with the 5.0.6.3 release. > For the current 5.1 releases, the StartCenter accelerators have been > reassigned--any dev work or BZ reporting should be against that branch. > > Stuart > > > > -- Thank you very much for your reply. In my prior email, I forgot to mention that testing with "Alt+d" generates the same behavior as with "d" alone; i.e. Writer 5.0.6.3 initially fails to open. Based on your comments, I performed the following test: 1_ With all LibreOffice closed, I opened LibreOffice 5.0.6.3 ("soffice.exe"). 2_ Press F10. The menu receives focus. 3_ Press F10 again. The sidebar receives focus. 4_ Press "d". Writer is successfully opened. I repeated the test, replacing F10 with Alt. Same behavior. I repeated the test, replacing "d" with "Alt+d". Same behavior. So my conclusion would be that the difference between the 4.4.x (and older) branch(es) and 5.0.6.3 is that the initial focus has changed. Wherever the initial focus is located in 5.0.6.3, the shortcut keys shown in the sidebar are _not_ valid for it. Changing the focus to the sidebar will allow the shortcuts to be correctly used. I am not ready to update to 5.1.x yet. I hope the shortcut keys will be valid immediately after opening LibreOffice (as it used to be in the 4.4.x and older branches), instead of having to move the focus first. Whether this means expanding the validity of the shortcut keys to additional areas of the user interface, or changing the default initial focus to be located on the sidebar, or some other alternative, I do not know. Thank you, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted