Re: [libreoffice-users] Using '+' as text only in tables
At 11:59 14/02/2024 +, Ian Graham wrote: I make a lot of use of tables, even though my various documents are almost exclusively text / *.odt. Tables in text documents are a useful but probably under-appreciated technique for formatting, even for material that you wouldn't consider a table. Something that trips me up from time to time is the effect '+' has in a table i.e. it turns it into a sort of basic spreadsheet. That is by design, of course. Is there a way of formating the table, or a more local control, that will allow the use of '+' purely as an alphanumeric letter/symbol ? o Select a cell, a range of cells, or the entire table. o Go to Table | Number Format... and set the format to Text. You can also disable the facility entirely by removing the tick from Tools | Options... | LibreOffice Writer | Table | Input in Tables | Number recognition. I trust this helps. Brian Barker -- 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] stripping crlf
At 11:08 03/01/2024 -0500, Bill Drescher wrote: I have a document that I copied from the original on the web (legally). Now I need to modify it using LO. The document has hard line breaks that I want to remove. The first thing to do is to understand what you are handling here. Calling these "hard line breaks" (or, even worse, "crlf" - as you have in your Subject header) is unhelpful, as what I assume you have here is what in a word processor are *paragraph breaks*. Using the find and replace form I can not figure out how to indicate a line break. It's generally necessary to think in terms of the paragraphs themselves, rather than breaks. (But perhaps not here ...) With "Regular Expressions" selected /n, /n/lf fail. Those would need to be backslashes, not slashes. And \n matches a line break, not the paragraph breaks that I believe you have here. What can I use to replace the line break with a space? Just "$" (no quotes), it seems. Another thought: consider using Paste Special (instead of ordinary Paste) and "Unformatted text" when inserting text from elsewhere. This avoids some problems. I trust this helps. Brian Barker -- 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] Tab Settings
At 14:51 28/12/2023 -0500, Charles Meyer wrote: Brian wrote... The solution, where you need to use tab stops in this way, is to set them specifically where you need them... How do you set tabs where you need them? Steps? Screenshots? Steps? Screenshots? No: rather, documentation! See "Setting tab stops and indents" in Chapter 4, "Formatting Text", of the Writer Guide. Or see the on-line version: https://help.libreoffice.org/7.6/en-GB/text/shared/guide/tabs.html . Or, perhaps better, see "Using tables for page layout" in Chapter 6, "Formatting Pages: Advanced", of the Writer Guide. Brian Barker -- 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] Tab Settings
At 18:40 27/12/2023 -0500, Charles Meyer wrote: I want to set tabs so when I press the Tab key the 1st goes over to the right a bit to the right then when I press the Tab key again it moves the cursor incrementally to the right and so on. At 00:21 28/12/2023 +, Prof. W. Robert J. Funnell wrote: ... my LibreOffice says 1.25 cm, meaning that I have tab stops set every 1.25 cm across the page. This was the default setting. It seems to be what you want, ... At 20:19 27/12/2023 -0500, Charles Meyer wrote: ... 1.25 cm works great. May I suggest that this is not best practice? It is completely understandable that default tab stops should be set at regular intervals in the application in this way. But users wishing to align material in columns often then fall into the habit of pressing the Tab key multiple times to move from material in one column to the required next column. The number of tab characters required will depend on the length of material in each row (paragraph) of the earlier column, of course. Everything looks tidy, and will be if you choose to print it. But what happens if you change the font or font size of the material (specifically the material in the earlier column)? The extent of the earlier material may increase or decrease, taking it beyond a tab stop or before a tab stop. Either way, the number of tab characters now required will be different, and without further corrective editing the later column will no longer be properly aligned. (If you don't believe me, you can easily test this.) Now you will say that you are unlikely to want to make such changes - and that may well be true. But there is a bigger problem. If you send your text (word processor) document to someone else, the fonts you use are not themselves transmitted - only their names and details of font size and so on. When the document is rendered on the distant system, a different font with the same name may be used, or even a different font, chosen according to font substitution rules. In either case, the earlier text may take up more or less space, and the later column may again not be properly aligned. Your correspondents will think you are a poor editor and cannot lay material out neatly. The solution, where you need to use tab stops in this way, is to set them specifically where you need them, so that only one tab character is needed between columns, however long or short the text in preceding columns may be. That way, your documents are far more likely to be rendered appropriately. An alternative is to use tables, which are a flexible and effective formatting tool for columnar material, not restricted to material that you would readily think of as a table. Brian Barker -- 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] Scalc I cannot merge cells, also I selected and unprotected them
At 18:31 11/10/2023 +0200, Uwe Brauer wrote: I have a document with several sheets, I selected some cells on a row, made sure they are unprotected, and then selected the merge option, however that option remained in grey and could not been selected. What is the problem here? Do you have Edit | Track Changes > | Record selected? Toggle it off. I trust this helps. Brian Barker -- 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] Odd find and replace problem
At 12:35 09/06/2023 -0400 Dotty Peter wrote: I'm trying to replace this- UT1 with this- GMT However, when using Find and Replace I get this- UT1GMT How can I fix this? Turn off Track Changes. I trust this helps. Brian Barker -- 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] Adding Time in hours
At 06:29 25/05/2023 -0400, Dotty Peter wrote: In adding time of hours and minutes, when the total is greater than 24 hours, the total reduces to minutes. For example; 23hours 45 minutes PLUS 3 hours 15 minutes Total- 3 hours That's because you have the sum formatted as HH:MM or something similar. That's a clock time, not a time interval. And three and a quarter hours after a quarter to midnight is indeed three a.m. I need the sum total hours no matter if it's beyond 24. Try [HH]:MM (or similar) instead. I trust this helps. Brian Barker -- 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] Is there a way to have cell format remain with an external link??
At 00:45 19/05/2023 +1000, Michael D. Setzer II wrote: Have a spreadsheet that has internal link that is updated on loading. 6 of the columns have numbers with either 1 or 2 decimal places. But when it imports the cells change to display only the non-zero decimals. So numbers that have x.00 are displayed as x, and ones with x.y0 are displayed as x.y. Tried selecting column to format, but on next update it goes back to the general format. Could create a macro to redo formatting, seems there should be a method. Other columns have whole numbers so changing spreadsheet default would no be a solution. I imagine that you have set up the linkage to a named range in the source spreadsheet, and it appears that doing it this way the formatting of the source data is carried over - which is not what you want. Here are two thoughts: 1. There is an alternative way to arrange linkage: just enter "=" (no quotes) into a cell and then click on the relevant source cell in the other spreadsheet. This is just as you would do within the same spreadsheet, of course, and - unlike the other method - it creates a formula in the cell. It is easier than it sounds, as you can fill such formulae into other cells in the required range. This technique appears not to carry over the cell formatting, so it may achieve what you desire. 2. In any case, there is a workaround. Create your link in whatever way is convenient. Don't worry about the formatting of the values. In another column, make copies of the values using the "=" technique. Format this new column as you wish. If preferred, hide the original column. I trust this helps. Brian Barker -- 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] does this work for you?
At 16:44 05/05/2023 -0400, James Lockie wrote: That explains my test spreadsheet and I fixed that in the test and it works. Incidentally, one easy way to spot that what you have is text and not a proper date is that by default text is left-justified - as in your original test file. True dates (just like numbers, which is what they really are) are by default right-justified in the cell. I trust this helps. Brian Barker -- 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] does this work for you?
At 15:41 05/05/2023 -0400, James Lockie wrote: Does this work for you? The A1 cell contains a date with a date format (NN, MMM D, ) that displays the weekday month day year. The B1 cell contains a reference to the A1 cell but has a date format () that is only supposed to display the month. I uploaded a sample file ... Although you have your cells formatted as you suggest, what you have in A1 is actually a text string and not a date. When this is referenced in B1, that also will contain the same text string. Since you have no dates, your formats are ignored. In order for your typing (in A1) to be interpreted as a date, it needs to be something that LibreOffice will interpret as a date. In that case, the string you type will be converted to a date in the cell, and this will be displayed according to the cell format. It is not necessary to enter such a date in the format you wish to see; indeed, in this case, you must not, since that is not one of the formats that LibreOffice will recognise. Just re-enter the date in A1 in such a format (e.g. 1/3/24 in your locale) and everything will spring to life as you wish. I trust this helps. Brian Barker -- 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 improvement for review?
At 04:35 27/03/2023 -0700, Don Wilde wrote: I was attempting to add my trademark claims to the footer of a document describing some inventions, and LO Writer assumed I was entering an e-mail address because my trademark included the '@' sign. You mean it automatically created a hyperlink? I ended up making images of my TM in Inkscape and GIMP and inserting those, but it seems to me that a more general solution would be appropriate. Why not just use the facilities available? People are GOING to make 'words' with special characters, as I did. This will become more common! I'm not sure that is true - or why it is relevant: you need a solution even for your single occurrence. There are various techniques that solve your problem: o If you do not want URLs to be recognised and converted to hyperlinks, go to Tools | AutoCorrect | AutoCorrect Options | Options and removed the ticks from the URL Recognition option. o If you want to remove a hyperlink after it has been created, just right-click in the hyperlink text and select Remove Hyperlink from the context menu. o Most simply of all, as you are typing and immediately the hyperlink is created, use Edit | Undo (or Ctrl+Z). This will undo the automatic correction but retain the typed text. I trust this helps. Brian Barker -- 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.
At 17:45 10/02/2023 +0100, Johnny Rosenberg wrote: At 12:29 10/02/2023 -0300, Ady Noname wrote: 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. 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. But hold on! Anything starting with an equals sign is regarded as a formula. And formulae are *always* edited by a spreadsheet program after entry. In particular, names of functions such as IF() and AND() can be entered in lower case - if() and and() - but these are edited to upper case by the program. Trailing spaces seem always to be trimmed. And =TRUE is edited to =1 - even though the result may be displayed as TRUE if the cell format requires this. If I enter =002+003, the formula is edited to =2+3. If what is entered is pretending to be a formula but is actually nonsense, is it so surprising or worrying that the edited result is equal (but slightly different) nonsense, also masquerading as a formula? Brian Barker -- 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.
At 13:33 07/02/2023 +1300, Steve Edmonds wrote: I am observing what may be a bug in saving a formula in a cell where additional spaces are repeatably inserted on editing the formula. If someone could replicate this I will file a bug. Create a sheet with cell contents as below. A3 is =IF((F22=$B$21) AND (G22<>$B$21),10,20) I'm not sure why you would want this in a cell, as it makes no sense. Do you perhaps need =IF(AND(F22=$B$21,G22<>$B$21),10,20) instead? I trust this helps. Brian Barker -- 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] How to copy text INCLUDING format settings but WITHOUT the header and background settings
At 16:30 01/10/2022 +0200, Martin Deppe wrote: I am using libreoffice version 7.3.3.1 and I have a - let me say - weird situation when copying text from one document with a header and a background image to another document without that. The background image AND even the header is copied along with it, which I definitely don't want or need. Has anybody an idea how to disable or get around this - for my sakes - strange behaviour without having to renew all format settings of that copied text when copying it unformatted? Easy: use Edit | Paste Special... (or Ctrl+Shift+V) instead of normal Paste, and choose "Unformatted text" from the options in the Paste Special dialogue. Alternatively, you can use Ctrl+Alt+Shift+V, which pastes unformatted text directly. The text you paste will inherit the formatting of surrounding material. I trust this helps. Brian Barker -- 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] How to print a section of a SS
At 17:45 19/10/2021 +0100, Dave Howorth wrote: On Tue, 19 Oct 2021 13:05:10 +0100 Brian Barker wrote: At 11:53 19/10/2021 +0100, Dave Howorth wrote: On Mon, 18 Oct 2021 20:51:19 -0400 Alan Bonly wrote: It hasn't been mentioned if the OP is printing to PDF, and my bad for never reporting the bug, but using LibreOffice File>Export as PDF or the Export Directly as PDF icon causes EVERYTHING on all tabs to print to the PDF no matter what print range might be specified, no matter if the print ranges are deleted then selected anew. EVERYTHING gets dumped into a PDF. I don't think that's a bug. [...] The documentation thinks it is a bug. Under "Using print ranges", it says: "You can define which range of cells on a spreadsheet are to be printed or exported to a PDF. The cells on the sheet that are not part of the defined print range are not printed or exported. Also, any sheets without a defined print range are not printed or exported." The documentation thinks what's a bug, exactly? Alan claimed that if you try to print as a PDF then everything gets printed, without any choice. You appear to be stating that the documentation coincides with reality, which is exactly the opposite and what I said. Thus what Alan claimed is a bug is NOT a bug. Or are we somehow at cross purposes? I think you do misunderstand me; perhaps I was a little brief. The original claim was that exporting to PDF takes no account of defined print ranges and exports the entire document. (I didn't check that or experiment with it.) We are not talking about the separate facility, in the print and export dialogues, to print or export the currently selected range, are we? You suggested that it was not a bug: in other words, that you expected export as PDF should disregard defined print ranges, as we were told it did. But the documentation about print ranges makes clear that they are supposed to apply equally to printing and exporting, in other words that exporting to PDF should export only material within any defined print ranges and not other material. Brian Barker -- 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] How to print a section of a SS
At 18:20 19/10/2021 +0200, Philip Jackson wrote: On 19/10/2021 14:05, Brian Barker wrote: The documentation thinks it is a bug. Under "Using print ranges", it says: "You can define which range of cells on a spreadsheet are to be printed or exported to a PDF. The cells on the sheet that are not part of the defined print range are not printed or exported. Also, any sheets without a defined print range are not printed or exported." I find it exports to pdf as advertised. If I select and define a range on one of the sheets, just that gets exported ... Good: so you are disagreeing with the original claim (which I did not check). In that case, there's no bug. Brian Barker -- 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] How to print a section of a SS
At 11:53 19/10/2021 +0100, Dave Howorth wrote: On Mon, 18 Oct 2021 20:51:19 -0400 Alan Bonly wrote: It hasn't been mentioned if the OP is printing to PDF, and my bad for never reporting the bug, but using LibreOffice File>Export as PDF or the Export Directly as PDF icon causes EVERYTHING on all tabs to print to the PDF no matter what print range might be specified, no matter if the print ranges are deleted then selected anew. EVERYTHING gets dumped into a PDF. I don't think that's a bug. [...] The documentation thinks it is a bug. Under "Using print ranges", it says: "You can define which range of cells on a spreadsheet are to be printed or exported to a PDF. The cells on the sheet that are not part of the defined print range are not printed or exported. Also, any sheets without a defined print range are not printed or exported." Brian Barker -- 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] Question [not!] on INDIRECT()
At 01:13 18/10/2021 +0200, Rob Jasper wrote:
I have the following question:
If, as here, you prepare your question as a reply to a previous
message when it is nothing of the sort, it will be lost in the
previous thread and may not be seen by possible helpers.
I have in column A value either a number, or string in the
format +, ...
Nauru? Norwich postcodes? National Rail? Noise reduction?
... in col B I want to calculate what's in Col A
e.g.:
1010
12+72 84
3333
66+72 138
So, I tired the formula =INDIRECT("=") but I only get REF! What
am I doing wrong here, ...
Probably thinking INDIRECT() does something different from what it does.
... and how can I achieve what I want?
First solution: don't enter your data like that in the first place.
I don't think there is any simple method to do what you ask in a
general fashion. If, as you suggest, the operator is always "+", you could use
=IF(ISNUMBER(A1);A1;LEFT(A1;FIND("+";A1)-1)+MID(A1;FIND("+";A1)+1;99))
You could make this simpler if you knew that the numbers in the
expressions were always of two digits.
I trust this helps.
Brian Barker
--
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] unwanted red lines on spreadsheet and info boxes
At 00:40 05/10/2021 +0200, Philip Jackson wrote: Suddenly in the middle of a sheet of a newly created spreadsheet, it has developed a rash of red lines. Some enclose a range of cells and some just appear on the whole row. If I hover over these red lines, a small yellow info box opens which informs me that I, at date, time, deleted rows xx:yy. Or if the line is a box enclosing a range of cells, the note tells me that on date/time I moved Range A12:B12 to C13:D13. I don't know what I did to cause these lines to appear and what's worse. I don't know how to make them vanish. To suppress the recording (and display) of changes, toggle off Edit | Track Changes > | Record. I trust this helps. Brian Barker -- 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] extracting cell values
At 12:02 25/09/2021 -0400, Miss Keating wrote: Is there a way to get LO (v5.2.7.2) calc to extract the numerical value from a cell, and use that instead of the cell reference in further calculations? Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I program it, (if at all)? If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it will display as 16.7. I want to get away from cell references and use the contained values. Can I? I thought that =VALUE(A1)/10 might work, but the result continues to use the cell reference, not the contained value. I *think* this is a contradiction, as any formula you put in A2 - whatever it is - will necessarily continue to refer to A1 and its value be changed when the value in A1 is modified. That's what spreadsheets do! But there is a workaround: o Enter your formula as usual. o Fill it down columns or across rows as appropriate. o Select the range of cells that you wish to freeze. o Copy that range. o Paste the values back into the same range, but using Paste Special instead of ordinary Paste, and ensuring that Formulae is *not* ticked in the Paste Special dialogue. Ana alternative would be to make a copy of your original range of data values and freeze those. Then you could base your subsequent calculations on the cells containing those frozen values instead of on the originals. The copied values could be visible on the same sheet, or hidden away elsewhere on the same sheet or another sheet. You could use Print Ranges to suppress printing of whichever values you did not want to see. I trust this helps. Brian Barker -- 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] Consolidating values from multiple sheets
At 22:29 17/08/2021 +1200, Martin F Krafft wrote:
I've got several sheets with values such as the following:
| Thu | Fri | Sat |
|:|:|:|
| 5 | 6 | | | | 9 | 10 | | 1 | | 2 | | | 9 | 10 | | 11 | | 12 | | 7
| 8 | | | | 3 | 4 | | 11 | | 12 |
Here is another
| Thu | Fri | Sat |
|:|:|:|
| g | h | | | a | | b | | | i | j | | g | h | | | e | f | | | | c |
d | | k | | l |
Sorry, but I have no idea what this picture represents. It is usually
more helpful to explain how your sheets are organs.
How would one go about creating a new sheet that consolidates all
these together, so that I'd get a list of all the values for each of
the columns, i.e. an array with the values {5,1,11,7,11,g,a,g,e,k}
for "Thu", and so on, ...
Er, exactly how do those become the values for Thursday?
o Enter "=" (no quotes) into the target cell in your new sheet.
o Click the tab of your source sheet to display it.
o Click the source cell.
o Press Enter (or click the Accept green tick icon in the Input Line).
You can now drag the cell's fill handle in the target sheet to expand
the area referenced to a row, a column, or another range.
I trust this helps.
Brian Barker
--
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] Better approach for formatting
At 11:41 09/08/2021 -0400, Michael Tiernan wrote: I have a request to create a table-like entry in Writer that would look like this: Revision Date Author Description so that if done, it would look sort of like this: (Newest on top). V1.4 21-July-80Fred Farkle Long verbose description of all the brilliant changes put back in.. V1.3 20-Jul-80 Peter Pickle Deleted long verbose descriptions put in by Fred "Purple Prose" Farkle. V1.2 19-July-80Fred Farkle Added long verbose description of all the brilliant changes put back in. (Multiple lines of content here.) I did it and passed it back for the other person to go on with. Good: so you've already achieved what you want! BUT, I started wondering if there's a "better" way to do such a thing. That depends on what your "thing" is. Unfortunately, just giving a picture of what you want the document text to look like doesn't explain what you want the document to do. If all you need is that it should look right, you have already achieved this - however you did it. In your e-mail message you appear to have used multiple consecutive space characters to arrange positioning; it would be unwise to do this in a word processor document, but instead to use proper facilities. (I recognise that you may have done things properly in your text document but had to use spaces in the mail message.) I thought one method using paragraph styles to do the work but it seems like the wrong tool for the job. What was your "method using paragraph styles"? Every paragraph has a style, so you are always using paragraph styles. It cannot be the wrong tool. I considered a table but it doesn't seem to fit the task either. Er, and why not? Tables are often the answer. ... I'm wondering how, without changing the required format, how would you make such a thing in a Writer document? o Create a table with three columns and sufficient rows. (Tables do not need to have visible borders.) o Enter your three heading items in each odd row. o Select all the cells of each even row and use Merge Cells to create a single cell on that row. o Enter your long description in that single cell. If, as it appears, you may want your long descriptions to be indented, then you would want to achieve this with a suitable paragraph style. The standard "Text body indent" may suffice. Again, you haven't indicated what you might want to do with this document, so I'm having to guess that you may want to add entries at the top of the list. That would be easy: o Put the cursor into the first row of the table. o Insert two additional rows, using the Before option. o Merge the cells of the new second row. I trust this helps. Brian Barker -- 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] Quote marks
At 12:17 06/08/2021 -0700, MR ZenWiz wrote: I have an ODT document in which I have specified the quotes to be straight quotes (as opposed to the curly opening-and-closing "matched" set). For some reason I can't explain, any new quotes inserted in the document show up as curly quotes, not straight. Surely the action of AutoCorrect? I have gone into Tools -> AutoCorrect -> AutoCorrect Options -> Localized Options and set both single and double quotes for the straight quote marks, and it seems to have no effect. Not sure what you mean by "set both single and double quotes for the straight quote marks". Surely you just remove the ticks from the Replace boxes to disable this form of correction - no "setting" involved? I trust this helps. Brian Barker -- 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] calc charting issue
At 15:18 31/07/2021 -0700, Dave Stevens wrote: I have a small spreadsheet 5x12 elements and the numeric content data is missing for some cells. Their contents are given as ND for No Data. I want to chart these 5 rows and at first thought I'd just set them to zero. The range on y-axis values is about from 5 to 15. But with zeros the chart line takes a dive to the x-axis then back up. I think a better visualisation would be for the graph lines to be discontinuous where no data exists, precluding zeroing or smoothing over the gap (usually only one missing datum) I don't see a handy way to do this, does someone with more experience see how? I'll read instructions if pointed to them. Yes: if you set your cells to zero, they will indeed be taken for zero and plotted as such. You need to have your missing data cells genuinely empty. I'm surprised if that doesn't give you what you require by default. If not, right-click on one of the points in a relevant line in the chart and select Format Data Series... | Options | Plot Options. Set "Plot missing values" to "Leave gap" (or as preferred). I trust this helps. Brian Barker -- 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] Start Date
At 12:41 14/06/2021 +0200, Silvio Siefke wrote: I have a table like: 11.04.2022;service a;250 12.04.2022;service a;250 13.04.2022;service a;250 14.04.2022;service b;250 15.04.2022;service b;250 12.05.2022;service b;250 13.05.2022;service c;250 14.05.2022;service c;250 17.05.2022;service d;250 18.05.2022;service d;250 Service a need 10 meters par day. Service b need 1 meters par day. Service c need 3 meters par day. Service d need 5 meters par day. This mean 11.04.2022 - 25 working days = Project Start Day. The date is enddate, I need the start day based on service values of meters and only working days. Is there a way? I think you've been given all the answer already, but it may be helpful to spell it out. o I hope the date column contains genuine date values and not just pieces of text that look like dates. o You would be wise not to include the word "service" in all the second column entries. If you do, you will inevitable misspell the word at some point and possibly not notice, generating errors in your calculations. Put "service" as a column heading and just the relevant letters - a, b, c, or d - in the cells. o If the third column always has 250, you don't need that column at all. Either use the 250 value in your formulae or else, if it might ever be changed, put it once somewhere in your spreadsheet and refer to that single cell in your formulae. o I'm guessing (you haven't exactly said) that the length of each process is 250 divided by the "meters per day" for each service. That works simply for services a, b, and d, but service c requires eighty-three *and a third* days. You must decide whether, in your unexplained context, you require that to be truncated to eighty-three or expanded to eighty-four. Since your final answer is just a date, it has to be one or the other. You will need to modify any formulae to deal with that question. o Let's suppose you have dates in column A and a, b, c, etc. in column B. Construct a table elsewhere - let's say in columns M and N - with a, b, c, and d in column M and 10, 1, 3, and 5 in corresponding cells of column N. Then =VLOOKUP(Bn,M$1:N$4,2,0) would retrieve the appropriate value for each line in your data from the table. So your start date becomes =An-250/VLOOKUP(Bn,M$1:N$4,2,0) if all days count, or =WORKDAY(An,-250/VLOOKUP(Bn,M$1:N$4,2,0)) if only Mondays to Fridays count. You should wrap something around the 250/VLOOKUP(...) part to cope with the fractional part I identified earlier. These formulae will produce numbers, so you will need to format the result cells (column) as Date in order for the result to show as meaningful dates. PS: You are too late to start those service B processes! I trust this helps. Brian Barker -- 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] Updating Data in Calc
At 15:01 13/06/2021 +0200, Hylton Conacher wrote: A simple sum will do it i.e.: In cell Y4 insert [...] =SUM(M18-$W$4) I'm often puzzled by suggestions such as this. In what way do you think that =SUM(Xm-Yn) differs from =Xm-Yn? The minus sign effects the subtraction between two values, generating a single value as its result. You then pass this single value to the SUM() function. What do you expect that function do with a single value? The sum of two and two is four; what do you think is the sum of just two? Brian Barker -- 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] Updating Data in Calc
At 11:20 12/06/2021 -0400, Vince Bonly wrote: I would greatly receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes. The formula in $Y$4 =$M18-$W$4. Generally speaking, this process is automatic in spreadsheets, of course. If you change the values in cells that are referenced in formulae elsewhere, the result of those formulae will immediately change. At 11:46 13/06/2021 -0400, Vince Bonly wrote: Perhaps I should have written in my OP that "The formula /presently/ in $Y$4=$M18-$W$4". I expect that that formula, obviously, must be changed to reach a solution to my problem, ... That formula was the only hint anyone had as to what you are trying to achieve. If, as you now say, it does not define your need, there is absolutely no indication so far in what you have given for anyone to go on. You are seeking a solution to a problem you have not been prepared to state. If you put some formula in Y4 (in this context it makes no sense to call it "$Y$4") that depends on (some? all of the?) values in column M, its result will change when those source values change. Whether you need =SUM(M1:M99) or =MAX(M1:M99) or =VLOOKUP(M4,X1:Y10,2,FALSE) or =IF(SUM(M1:M99)>0,"Hooray: I'm solvent!","Shucks, I'm bankrupt.") or one of a thousand other possibilities we can only guess. I trust this helps. Brian Barker -- 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] Multiple references to footnote?
At 13:42 02/06/2021 -0600, Joe Conner wrote: This seems to me to be a use of ibid. Maybe. "Ibid." is short for "ibidem", which is Latin for "in the same place" - or "there again". So that works perfectly well if the footnote is a citation, but not if it is some elaboration or explanation of the footnoted text. It doesn't mean "ditto". Brian Barker -- 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] adjust image automatically
At 22:45 31/05/2021 +, Jessica Jones wrote: i'm not making a text document! Only a drawin in Draw. I recognised this. But, as I explained, you haven't indicated why your document needs to be a drawing. Depending on what you are trying to achieve, you may be better off creating a text document, which can easily contain your images. No-one can help you fully whilst you keep your purpose a secret. Zince you talk ablut writer, it doezn't help me at all On the contrary, it *may* have been just the help you need. Brian Barker -- 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] adjust image automatically
At 20:04 30/05/2021 +, Jessica Jones wrote: I'm using libreoffice draw and I was wondering if this feature exists somewhere. I want to make a rectangle of a certain size. Inside this rectangle I want to show a flag of a country. The image of the flag is from google images (it's a jpg or png image) and I want libreoffice draw to adjust the image size to the rectangle size automatically so the image fills the rectangle correctly.So I want to make this drawing for all countries and don't want to resize the image myself each time. I want to assign a new image so it's automatically resized correctly to fill the entire rectangle. Is this possible in libreoffice draw and how? I'm a beginner so I need step by step instructions. You don't say why you are choosing to create a drawing (Draw) document rather than some other type. What else does the document contain, apart from the flag images? If this is text, you might prefer to create a text (Writer) document instead. Use a table or tables to lay your text document out. I think you will find that any image pasted into a table cell will be automatically scaled to fit the width of the cell, which achieves just what you require. Note that table borders can be adjusted so that they either show or not in different places; the table structure does not need to show at all in your finished document, if that is what you prefer. Tables are described in Chapter 13 of the Writer Guide. I trust this helps. Brian Barker -- 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] conditional formatting based on day of week
At 18:01 28/05/2021 -0400, Gary Dale wrote:
It's been years since I tried to do anything with conditional
formatting and now I can't seem to get it right. I have a
spreadsheet sorted in date order where I want to highlight the start
of each week (i.e. Sunday) to divide up the data visually. My idea
was to test if the date cell in the current row was a Sunday, so I
constructed the formula as:
(WEEKDAY(ADDRESS(ROW(), "B"), 2) = 0)
where column B contains the dates. I apply that condition to a
selection that excludes the column titles and set the formatting to
a background highlight. Unfortunately it doesn't do anything and I
can't figure out why. Any ideas?
Yup! There are a number of faults here:
o The ADDRESS() function requires the *number* of columns and rows,
not their letter designations, so you need 2 for the column, not "B".
o The ADDRESS() function requires a third parameter, indicating what
sort of cell reference you need. It probably doesn't matter which you
choose, but you do need to specify it. Choose 1 for absolute, producing "$B$n".
o The result of the ADDRESS() function is text, not a cell reference.
You would need to apply the INDIRECT() function to the text in order
to interpret it as a cell reference. But the INDIRECT() function will
do the work for you, so ditch the ADDRESS() function and use
INDIRECT("$B$"())
instead.
o The WEEKDAY() function never gives 0 for Sunday. Instead it gives 1
if Type is 1 or omitted and anything else you want between 1 and 7
depending on Type, but never 0.
o The containing parentheses are unnecessary and probably confusing.
Try something like:
WEEKDAY(INDIRECT("$B$"()))=1
I trust this helps.
Brian Barker
--
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] auto edit youtube transcript?
At 20:39 15/05/2021 -0700, Dave Stevens wrote: Here's a snippet of a youtube auto-generated CC transcript. Can someone show how to delete the lines that have times? as far as i know they are strictly alternating and have exactly 1 colon, but that's where my smarts end. Anyone have a suggestion? If you indeed just need to remove all odd-numbered lines of the text, you could do this: o Paste the material into column A of a spreadsheet, starting at A1 - so that each line occupies one cell. o In cell B1 (say), enter =OFFSET(A$1,ROW()*2-1,0) o Fill down column B. I think this works whether your original "lines" are separated by line breaks or are actually separate paragraphs. You can copy and paste the material in column B back into a text document for further formatting as required, of course. I trust this helps. Brian Barker -- 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] cells donât auto-wrap
At 14:11 06/04/2021 -0400, Eric Beversluis wrote: I've got a spreadsheet with all cells formatted to auto-wrap, yet when I enter stuff into certain cells, it doesn't autowrap. And sometimes the cell just shows the last bit of what I've entered. When I right-click and check the cell, it does show it's set to auto-wrap. I think the answer may be simple: that you have set the row height for the relevant cells to be insufficient to allow wrapping to happen. In this case, the last line of wrapped text is indeed what will appear in the cell. You need to tinker with row height to enable Calc to expand the row sufficiently to accept all your text. I trust this helps. Brian Barker -- 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] days since beginning of year
At 18:54 04/04/2021 -0400, James Lockie wrote: How do I get the number of days since the beginning of year? At 00:29 05/04/2021 +, James Lockie wrote: How do I get the start date to be dynamically the current year? At 21:04 04/04/2021 -0400, James Lockie wrote: How about this? =DAYS(CONCAT(YEAR(TODAY()),"-",MONTH(TODAY()),"-",DAY(TODAY())),CONCAT(YEAR(TODAY())-1,"-12-31")) I think you have dismantled today's date and then reassembled it. Perhaps, more easily, this? =DAYS(TODAY(),DATE(YEAR(TODAY()),1,1))+1 Or this? =DAYS(TODAY(),YEAR(TODAY())&"-1-1")+1 I trust this helps. Brian Barker -- 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] Counting cells
At 21:46 24/03/2021 -0600, Joe Conner wrote: I am confused about how to count a number of cells that have a number. In Calc, if cells a1:a11 each may or may not have a number in it, what formula may I use to count how many cells have a number? I do not want the sum of the numbers, just how many cells contain a number. This all depends on what you mean by "a number"! Do you just mean that the cell has something in it, so presumably a number - just anything, i.e. that the cell is not empty? If you mean specifically a number, do you include an explicit zero? And how about a cell that has a blank or blanks, which would be interpreted the same way as zero in many calculation contexts? Note that a cell containing blanks is different from an empty cell; you may or may not wish to include such cells. You probably won't want to include text strings such as "three", even though that is a number in one sense; but how about a cell containing a number expressed as digits but in the form of a text string, e.g. the character "3" as distinct from the numerical value 3? (This may arise from entering a number into a cell formatted as Text or by entering a leading apostrophe - or by pasting material from elsewhere.) The COUNT() function ignores empty cells and those that contain text, so may well be all you need. Otherwise you may like to consider formulae involving COUNTA() or COUNTIF(). Note also that it is sometimes easier to calculate the inverse of something, so you could consider using COUNTBLANK() (which should really be called COUNTEMPTY(), since it fails to include cells containing explicit blank spaces), and subtracting the result from the number of cells in the range. Exactly what you choose to do depends on how resilient you want your spreadsheet to be to rogue or erroneous cell contents as a result of later errors on your part (we all make them) or on the part of others who may use the spreadsheet. I trust this helps. Brian Barker -- 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] Re: File Explorer Day-week-month search for Libre files
At 00:41 24/02/2021 -0700, Luuk Noname wrote: <http://document-foundation-mail-archive.969070.n3.nabble.com/file/t331143/LastModifiedCalendar.png> Clicking just right of 'Date modified' still shows a calendar (I admit never seen that calendar before) I tried that a few hours ago (before I wrote) and it wouldn't show; now it does! Weird. Brian Barker -- 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] File Explorer Day-week-month search for Libre files
At 17:14 23/02/2021 -0500, Charles Meyer wrote: I'd like to be able to search in Windows 10 File Explorer for Libre Writer and Calc files by day wee month. On my work PC it was set up so when I open File Explorer there is a Date modified icon on the taskbar with a graphic of a calendar. When you click on it, a drop-down menu appears displaying: Today Yesterday This Week This Month Last Month This Year Last year I've Googled this and none of the results explain how one creates this Date modified icon on the taskbar. Does anyone recall how they created that in their File Explorer? Some facilities were apparently removed in version 1909 of Windows 10. The best you can do now is to start a search, whereupon the Search tab will appear in the ribbon, including a "Date modified" icon. If you click that, you will see a limited set of date possibilities: Today, "This month", "Last year", etc. I trust this helps. Brian Barker -- 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] Language setting in Calc
At 20:35 21/02/2021 +0900, Howard Barr wrote: I have been using English and Japanese both in msexcel and LO calc for many years. I receive an excel file and then make a duplicate. When I want to send a draft, I save it as an excel file and send it to the editor. I sometimes have problems when special characters are used. I use the input method frameworks fcitx and ibus. It allows me to flip between languages in any document. Libreoffice Version: 6.4.6.2 on Linux mint 20.0 and Language settings > languages > Default language for documents > Western -Default: English. Asian: Japanese I think you are at cross--purposes here. Yes, the questioner can enter English and Japanese text into different parts of a spreadsheet document; indeed, that is how the Excel-formatted documents arrive with him. He needs to add German, and I'm sure he can do this - umlauts and eszetts and all. So the fonts are OK. But what he would like is to keep the *language* settings for English and German applied to different columns - presumably so that the German text doesn't get marked as English misspellings! That works in LibreOffice, but gets lost when resaved into an Excel format. Brian Barker -- 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] Language setting in Calc
At 09:19 21/02/2021 +, Dave Howorth wrote: Apparently it is possible,since the OP originally stated: "The original file is so formatted that there are font settings for English and Japanese. When I work at home (right now) I select the relevant columns and change the language setting to German." For that to be the case, the original file must encode the language for individual columns. I think not. The questioner can change the language setting *in LibreOffice* for part of the spreadsheet document, because LibreOffice provides this facility. When you are editing a document file, you get the facilities the application provides, notwithstanding whether these can be saved in any chosen format - even the existing one. If the questioner then saves his work in .ods format (as he apparently now is doing), all would be well. But if he saves it back into an Excel format, the change gets lost, even when the document is reopened in LibreOffice. Yes: that could be a deficiency of LibreOffice's ability to save into Excel formats, but may instead be a deficiency in the Excel format itself. I have not been able to rule out the latter. The test, of course, is if someone can find instructions for doing this *in Microsoft Excel itself* or can find or produce a document file in Excel format which shows different language settings. Brian Barker -- 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] Language setting in Calc
At 15:09 20/02/2021 -0800, Nobody Noname wrote: On Sat, Feb 20, 2021 at 8:27 AM Thomas Blasejewicz wrote: The (Excel) file is the draft for the preparation of a questionnaire - to be translated into several languages (although the one I am currently working on has only 3 languages). Means: I want certain columns ONLY to be set to German, while others next to it are set to English and Japanese. Applying the language to the entire document does not do this trick. I did a little test and was able to reproduce your problem with the Column > Format Cells > Font > Polish (which is just the test language I used). I could reproduce the problem when working with the document as an .xlsx or .xls file, but the problem does NOT exist when I used a .ods file. So the issue seems to be specific to LibreOffice operating with Excel files. You might want to report it as a bug: https://wiki.documentfoundation.org/QA/BugReport Sorry, but you are assuming that this is a problem with LibreOffice's ability to save in a Microsoft Excel format, and that the fault is therefore LibreOffice's. But do we know that it is possible to save a spreadsheet document in Excel format with different columns marked as being in different languages? That's the same question, effectively, as "Can you set different languages for different parts of a spreadsheet in Microsoft Excel itself?" I don't know that you can. If not, the deficiency is with Microsoft's formats, and the bug report should probably go to them instead. Brian Barker -- 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] LO 6.4.7.2 Calc: Where is a cell referenced elsewhere in s file?
At 21:16 27/01/2021 +0200, Hylton Conacher wrote: I have a 134 column spreadsheet I am tidying up. An issue has arisen where if I delete a cell's content I do not know what other cell is referencing the deleted cell's content? How can I determine this ... Use Tools | Detective > | Trace Dependents (or Shift+F5). ...or should I raise a feature enhancement and if so where? Probably not. I trust this helps. Brian Barker -- 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] calc chart problems
n any forum - or indeed here on the Users mailing list - will be valuable or correct! How *exactly* can I add a 2nd Y axis and assign different scale to it? Following the given instruction to Double click on the chart, and then Menu/Insert/Axis, or right-click insert/delete axes leads to a menu which allows up to 2 each Y, and 2 each X, axes, ... Good: so tick Secondary axes | y-Axis. ... but shows no way to associate a set of figures with the added axes. It must also be possible to associate colours with the various traces, but how to do that remains equally a mystery. Not a mystery if you read the documentation! Click on the relevant line or whatever in the chart (not on a point) and select Format Data Series... | Options | "Align Data Series to" from the context menu. Colour is set on the Line tab of the same dialogue. It is worth saying that charts can be quite complicated, so you should expect first to obtain and consult the documentation and then to spend some time familiarising yourself with all the options. I trust this helps. Brian Barker -- 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] Re: Fit last column in spreadsheet to print
At 00:21 11/01/2021 -0500, John Kaufmann wrote: Sorry, but I don't follow: What does it mean for a spreadsheet to print one sheet wide? See "Printing options for page styles" in Chapter 6, "Printing, Exporting, E-mailing, and Signing" of the Calc Guide. o Go to Format | Page... | Sheet | Scale. o Set "Scaling mode" to "Fit print range(s) to width/height" and "Width in pages" to 1. ... (especially in an environment where a sheet could be a million columns wide) In that case, you would have chosen to make your printed data probably illegible! (Er, sorry, but maximum columns per sheet is 1024.) I trust this helps. Brian Barker -- 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] regex help
At 07:29 04/01/2021 -0500, Bill Drescher wrote: On 1/3/2021 4:09 PM, Brian Barker wrote: At 14:19 03/01/2021 -0500, Bill Drescher wrote: I have a script that mysteriously became double spaced. I want to search for 2 end of paragraph marks and replace them with one. So it's the paragraphs that are spaced, not lines (the usual meaning of "double-spaced")? Yes, I do mean lines are double spaced, but... If the lines of a paragraph really are double-spaced, you need to go to the paragraph or paragraph style formatting and change "Line spacing" from Double back to Single. Problem solved. In that case, you would not be - as you suggested - looking for anything to search for. If that is not your solution, you don't have double spacing and it would help you to understand what you do have (and indeed to explain your problem on the list) by using the appropriate terms. Remember that the concept of "lines" as you appear to be using it went out with lined manuscript paper or typewriters. I can't do what I want to do in my programming editor and when I read the script into LO the line endings are suppressed and each line is treated as a paragraph. If each line is a separate paragraph but they are still spaced too much, either you have empty paragraphs (not "lines") between your real paragraphs or you have paragraph spacing (in paragraph or paragraph style formatting) that you don't want. Remove paragraph spacing there. I don't want to remove all blank lines, just the ones that have a blank line following. In other words you want to remove blank empty *paragraphs*. Yes, but I have intentional blank lines that I do not want to remove. The only "blank lines" are created if you have consecutive *line breaks* between text. Is that really what you have? Oh, and if you are describing the so-called "blank lines" you want to remove the same way as the "blank lines" you want to preserve, how are we to know - indeed, how is LibreOffice to know - which is which? There has to be a specified difference if either a machine or an earnest human can distinguish them. Is there a way to select a paragraph followed by an empty paragraph? I'm not sure there is. But how would that help you? Here's a thought. (I'm guessing.) Do you actually have text that should run on within paragraphs but has somehow become separated such that each line of any paragraph has become a separate paragraph? If so, you may be able to reassemble paragraphs using AutoCorrect with "Combine single line paragraphs if length greater than ..." to a suitably small value. Should you be sending a sample document to someone (or to the list if it would be accepted) for diagnosis? I trust this helps. Brian Barker -- 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] regex help
At 14:19 03/01/2021 -0500, William Noname wrote: I have a script that mysteriously became double spaced. I want to search for 2 end of paragraph marks and replace them with one. So it's the paragraphs that are spaced, not lines (the usual meaning of "double-spaced")? I can do a regex search for $ and find them all, but $$ finds none - I presume there is something between them that does not show as a formatting mark. You are labouring under the common misapprehension that the pilcrow that indicates the presence of a paragraph break has an actual presence in the document; no: think of it merely as the indicator that it is. And you think that the $ symbol matches this; also no. I don't want to remove all blank lines, just the ones that have a blank line following. In other words you want to remove blank empty *paragraphs*. What is the regex for a paragraph mark in replace? There isn't one. Instead, the $ symbol merely locks your pattern, whatever it is, so that it will match only something that occurs at the end of a paragraph. Or, any other suggestions. Don't think of searching for paragraph breaks, which you cannot do. Instead, search for the empty paragraphs that you wish to delete. They contain nothing, so the pattern you need is also nothing. But you need to arrange that it matches nothing only if that nothing occurs at the beginning of a paragraph and at the same time at the end of a paragraph. The symbol for locking to the start of a paragraph is the caret, "^", so the pattern you should search for is "^$" (no quotes, of course) - replacing with nothing. I trust this helps. Brian Barker -- 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] Date number has 5 digits
At 09:27 29/12/2020 -0500, Peter Dutton wrote: In cell A4 is the following code; ="Monday"+January.$A5 Do you mean that plus sign - or perhaps an ampersand? January.$A5 has the number 27 in it (December 27). I'm not sure what you think you mean by adding "(December 27)"! What I suspect you have in that cell is not the number 27 but instead the date 27 December 2021 (which indeed will be a Monday) - but formatted so as to display only the numerical day part of that date. What appears in cell A4 as a result is- Monday 44557. Good. You are concatenating the text "Monday" with the value of that date, and in this context no regard is had to the formatting of the cell. The actual value in your cell for 27 December 2021 is 44557, which is the number of days from the start date until that day. What is needed is- Monday 27 I've tried reformatting cell A4 to a date or text or a number to no success. Reformatting a cell already containing a value does not change the value - merely the way it is displayed. Your cell still contains the number 44557. Any idea how to fix this? You need to extract the day number part of the date value before you concatenate it with the text "Monday". To do that, use ="Monday "(January.$A5) Depending on exactly what you need, have you considered using =TEXT(January.$A5;"D") instead? I trust this helps. Brian Barker -- 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] Superscript and Subscript Character Buttons in Writer :::----> Calc
At 17:59 28/12/2020 -0500, Vince Bonly wrote: I have been finding formulae at various math tutorial websites and pasting them into a Calc file, typically by first washing the text string through Notepad and/or LibreOffice Writer. Surely there is no need to "wash" anything if you paste as Unformatted text? My goal, in part, is to model a formula of interest within Calc, e.g., a^2 = b^2 + c^2 - 2bc Cos A; this is one way that allows me to learn how to manipulate Calc's functions. So, I used Writer's superscript button and then pasting the formula into my Calc file, as a text string, of course. A perfect task for tables in a text (Writer) document, not a spreadsheet. Time taken to learn the basics of tables in a text document: three minutes? Oh, and even easier: if you copy the range of cells you currently have in a spreadsheet and paste that into a text document, choosing possibly HTML or "Formatted text" in the Paste Special dialogue, a table will be created for you. Note that text tables are worth knowing about: they are frequently useful for formatting material in a text document that doesn't immediately appear to be tabular. I trust this helps. Brian Barker -- 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] bug in LARGE function?
At 20:09 25/11/2020 -0700, S T Moose wrote: in cell b4 to z4 are values 320, 320, 320, 320, 320, 320, 320, 320, 320,320, 320, 320, 320, 320, 320, 320, 322, 322, 323, 323, 323, 323, 323, 323, 323 in cell b8 formula =LARGE(B4:Z4,2) gives the result 323 (but I would think that it should return 322? - right? i am using the LARGE function incorrectly? I suspect it is working as it should. (In any case, as you have seen, this is what it does.) The largest number in your set is 323. The second largest is also 323, and so on down to the seventh largest. You need to get to the eighth largest before you get to the first occurrence (of two) of 322, and then the tenth to twenty-fifth largest are all 320. I trust this helps. Brian Barker -- 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] Suggestion: 3D spreadsheets
At 07:45 24/11/2020 -0700, Pierre Louis Boyer wrote: Something new an that would be really great is to add implementation for 3 (and potentially more) dimensional spreadsheets. What it would look like : Basically the same as a 2D spreadsheet. But by holding Alt+scrolling with the mouse, you could change the third dimension, showing the 2D spreadsheet of that third index. My spreadsheet documents have this facility in that they possess multiple sheets. I can even select multiple sheets at once, so that any action can apply to corresponding cells on multiple sheets. In other words, I am able to select a cell range in your third dimension. For instance let's say the third dimensions use Greek characters. Then by default you see the [alpha] 2D spreadsheet showing the numbers and letters grid at that value [alpha]. You hold Alt and scroll once and it changes to [beta] 2D spreadsheet. And so on. How does this differ from multiple sheets labelled "alpha", "beta", and so on? This could be really powerful. What do you think? I think you should write the code. Brian Barker -- 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] Question on value command and Err:502
At 14:48 24/11/2020 +1000, Michael D. Setzer II wrote: Put a more complex question, and got no responses yet. You have to admit that your description and layout were pretty foggy! Did anyone understand it? So, here it is in simplest form =value(1+1) converts the 1+1 to 2 But =value( ) fails with Err:502 if text "1+1" is used or if a cell containing the text 1+1 is used?? I've looked thru command, and this seems to be the one to convert a text string to a value? It is. But the text string "1+1" is not a value expressed as text but a numerical expression in text form. And VALUE() does not undertake to evaluate such expressions. Is there another command, ...; What you need instead is the EVALUATE() function. (PS: It doesn't exist!) I think the fact that VALUE(1+1) entered as part of a formula does what it does is a lucky side effect. Clearly in this case LibreOffice is doing something similar to the automatic editing that is done when any material is entered into a spreadsheet cell. I'm talking about this sort of thing: when you type "+02" (no quotes) into a cell formatted as Number, you see just 2. The "1+1" in this case is being entered into a cell (as part of a formula), not arising as the result of some calculation. I trust this helps. Brian Barker -- 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] Re: [Calc] Convert GPS coords from DMS to decimals?
At 10:02 22/11/2020 -0700, Gilles Noname wrote: Brian Barker wrote Yes, if your locale needs semicolons as separators commas will not do. That is definitely what error 509 will be signalling. Odd that the locale would change the sign used in functions. Odd, maybe, but true. Sorry that my formulae did not exactly match your needs. Brian Barker wrote That is because you have - weirdly - chosen to mess up my formula, changing my "E" (for east) into "O" (for west). So *of course* the longitude signs are now reversed. Reinstate my "E" and everything will work. Because in the document O = West, not East (which I forgot to point out). I know that, but it seems you don't! You didn't need to point it out: I had realised - and explained so in my original message. But you are still missing the point here. You thought you needed to translate my formula, but you didn't: I wrote exactly what you needed here. My test mentions "E" for east (or "est") but properly distinguishes between "E and "O". By changing "E" to "O" you have not translated into French but changed east to west. I'm sure east in France is the same east as anywhere else, and similarly west - no matter that the names are different! (Or do French compasses point southwards?!) You need "E" for French "est" in my formula just as you would need "E" for English "east". Surely you can see that swapping east and west (as you chose to do) will swap the signs of your longitude values and introduce the error? https://postimg.cc/QH0q5qmn Yes: I based my formulae on that original picture! Note that includes "E" for east/est. I trust this helps. Brian Barker -- 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] Re: [Calc] Convert GPS coords from DMS to decimals?
At 07:40 22/11/2020 -0700, Gilles Noname wrote: Problem solved by [...] running a regex to prepend a minus sign. Not proud of this, but it got the job done. Just reinstate the original (correct) "E" instead! I trust this helps. Brian Barker -- 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] Re: [Calc] Convert GPS coords from DMS to decimals?
At 06:48 22/11/2020 -0700, Gilles Noname wrote:
The issue with Error 509 was apparently due to
columns being of the wrong type (source should
be text, and target should be number), ...
No, it will not be that.
... and using "," instead of ";" in Left() and Mid().
Yes, if your locale needs semicolons as
separators commas will not do. That is definitely
what error 509 will be signalling.
... it's working
but the West/East isn't: Even
when LO finds "O" ("ouest" for West) in the
source, I'm not getting a negative as expected:
That is because you have - weirdly - chosen to
mess up my formula, changing my "E" (for east)
into "O" (for west). So *of course* the longitude
signs are now reversed. Reinstate my "E" and everything will work.
I trust this helps.
Brian Barker
--
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] Re: [Calc] Convert GPS coords from DMS to decimals?
At 05:20 22/11/2020 -0700, Gilles Noname wrote:
Brian Barker wrote
If 00°05'12"O 42°59'12"N is in A1, then
=(MID(A1,12,2)+(MID(A1,15,2)+MID(A1,18,2)/60)/60)*((RIGHT(A1,1)="N")*2-1)
will deliver 42.986667, and
=(LEFT(A1,2)+(MID(A1,4,2)+MID(A1,7,2)/60)/60)*((MID(A1,10,1)="E")*2-1)
will deliver -0.086667.
I tried this formula, but get error 509
("operator expected"). Maybe it's because the cell contains "Numbers"?
No, that's not what error 509 means.
https://postimg.cc/w764P7Rm
You've shown us the error message, but with focus
on a different cell, not showing the error. So
no-one can see the erroneous formula! I'm
guessing that your locale needs semicolons as
separators in place of the commas.
I trust this helps.
Brian Barker
--
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] [Calc] Convert GPS coords from DMS to decimals?
At 22:11 21/11/2020 -0700, Gilles Noname wrote: I need to convert GPS coordinates from degrees+minutes+seconds (DMS) to decimal, eg. 00°05'12"O 42°59'12"N -> 42.9867,-0.0867 Can Calc do this, ... Of course! I'm guessing that "O" means "east" (as in "ouest"?) and that the alternatives to "O" and "N" are "E" and "S" respectively? If 00°05'12"O 42°59'12"N is in A1, then =(MID(A1,12,2)+(MID(A1,15,2)+MID(A1,18,2)/60)/60)*((RIGHT(A1,1)="N")*2-1) will deliver 42.986667, and =(LEFT(A1,2)+(MID(A1,4,2)+MID(A1,7,2)/60)/60)*((MID(A1,10,1)="E")*2-1) will deliver -0.086667. The extracted parts of the original string are automatically converted from text to number on the fly when they are used in the arithmetical expressions. The equality tests in the last parts of the formulae are TRUE for north and east and FALSE for west and south. When used in an arithmetical expression, TRUE is interpreted as one and FALSE as zero. By doubling these and subtracting one, we get +1 for TRUE and -1 for FALSE, and we can multiply the calculated value by this number to attach the appropriate sign. This means that these formulae will also work for positions east and south of the origin - so your trip to the southern hemisphere will be covered. I trust this helps. Brian Barker -- 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] use calc 7 IF command to change some column values?
At 20:06 19/10/2020 -0700, Dave Stevens wrote: I have a column of 365 daily values, non-negative integers, (for a calendar year). I want a column (column 2) just like that but with some cells (start and end row specified) to be zeroed if they are too large. so... 1 1 3 3 21 21 29 0 22 22 0 0 All values in column 1 to be copied to column 2 and if greater than 28 replaced by zero. The help page gives a highly relevant IF function example that almost works. Oh, it does; it does! I just don't see how to write the value of the column 1 contents rather than the cell reference. The modified example is '=IF(A1:A13>28,0,"too small")'. at first I tried changing "too small" to A1 but I get "A1" in column 2 rather than a value. I don't see the syntax to get column 1's value rather than its reference. Just as in any other use of a spreadsheet, if you want the value of A1, you simply use A1 - without any quotes. If you are seeing the text A1 instead, you must be putting "A1" - with the quotes - in your formula. The example uses quotes around "too small" precisely because that is a literal piece of text and not a reference. Also, in the example, A1:A13 is a range of cells, but that is not what you need here. Instead, enter in B1 the formula =IF(A1>28,0,A1) and "fill" it down column B - by dragging the "fill handle" down the column to B365. The references to A1 change automatically to A2, A3, and so on. I trust this helps. Brian Barker -- 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] Static vs. dynamic time
At 02:37 02/10/2020 -0700, Chimel Noname wrote: I use the formula IF(A1<>"";NOW()) copied to every cell of column B. It gives me the correct time every time I type anything on a line of column A. But this is refreshed constantly by the time of the computer, not the time I typed something in column A. I tried looking for a static version of NOW() or playing with iterations (probably not correctly) or looking for a way to stop recomputing this specific column to no avail. Is there a way to set on column B the time of the typing on each line of column A and keep it as a static time. This is a simple question but unfortunately does not appear to have a simple answer. But - like with your other question - you can do something using a recursive formula. Enter into B1 =IF(A1="";"";IF(B1="";NOW();B1)) Once again, you need to have Tools | Options... | LibreOffice Calc | Calculate | Iterative References | Iterations ticked. This almost works, in that if A1 is empty and you enter something there, then B1 will change to record the time. But if you now simply overtype what is already in A1, B1 will not change. There is an easy workaround, however: before typing your new value into A1, just press Delete to empty A1 (B1 will now be empty too) and then enter your new value (B1 becomes the new time). So far, I am forced to type or insert the time manually each time. No longer! I trust this helps. Brian Barker -- 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] Static vs. dynamic maximum
At 06:41 02/10/2020 -0700, Chimel Noname wrote: ... I am also stuck with extracting the maximum value of one given cell. For instance, A1 contains variable numbers, and I want to store the maximal value of this cell in B1. As others have said, the cell, does not "contain variable numbers", but only the single value (or expression) that is there at the time. The trouble is that MAX() compares 2 different cells, whereas I want the maximal value of one cell. No, you don't. A recursive formula such as =MAX(A1;B1) would probably not work, and returns an error 523 indeed. Actually, this works: read on. At 07:18 02/10/2020 -0700, Chimel Noname wrote: A cell with a formula can have a whole "history" of values that change every now and then. No, the cell has just whatever is in it now. It has only one value at one given time, it's this value that I want to compare with its previous values so I can extract the maximal value the cell ever reached. But the previous values have been overwritten by the current one, so are no longer there. Actually, you have solved your own problem. If you want to keep a record in B1 of the largest of all the values that have appeared in A1, then putting =MAX(A1;B1) into B1 would appear to work - as B1 has kept a record of that part of the history of A1's values that you need. But the logic is recursive: evaluating the formula changes one of its parameters, so the formula potentially needs evaluating again. How many times should this happen? The process might never terminate. In this case, you want the calculation to happen once only, but how can LibreOffice know that? All you need to do is to permit recursive calculations, which you do by ticking Tools | Options... | LibreOffice Calc | Calculate | Iterative References | Iterations. Then your formula works. I trust this helps. Brian Barker -- 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] Exponential Power and Square Root Notations in Calc Formulae
At 17:23 24/09/2020 -0400, Vince Bonly wrote: LO: Version: 6.4.6.2 ... I am working with the Law of Sines and the Law of Cosines, and need to use formulas within Calc that include exponential powers. So, how is [...] written in Calc formulas notation? 1. n Squared See Lesson One in "How to use a spreadsheet program" - or perhaps Lesson One in Elementary Mathematics. 2. A = *sin*^*-1* **[ a*sin(B) / b ] Impossible. First, most of those asterisks make no sense. And the sine function makes no sense without any argument. Or do you mean what is in square brackets (stripped of some asterisks) to be the argument of the arcsine function? 3. Square Root of n See "square root" in the built-in help text. Or perhaps search https://help.libreoffice.org/6.2/en-GB/text/scalc/main.html . Or possibly see the answer to 1. Brian Barker -- 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] total newbie has Calc question
At 19:04 22/09/2020 -0700, Mamba Lev wrote: I'm putting together an annual rent report for the shared house i live in, due to varying room sizes there are two rent brackets (60/week for small rooms, 70/week for large) we also have one resident paying a third amount as part of an arrangement we have where he is doing some work for us. what i would like to be able to do is represent each rent bracket with a letter (eg "A" for small rooms, "B" for large and "S" for special arrangements) and by inputting that letter in to a cell i would like another cell to be populated with the total amount they owe in rent per year (or to be left blank in the case of the special arrangement). would this be possible and if so, how would i go about it? This is fairly straightforward. o Create a table of the values that you want to use. The first column of the table would have the code letters, A, B, and S, and the second column the corresponding values - with the cell against "S" being blank. This table can be out of the way of the main part of the spreadsheet, perhaps on a separate sheet or excluded from printing by the use of a Print Range, so that it will not appear on your printed report. o In the cells where you require the rent value to appear, you need to use the VLOOKUP() function, referring to the table you have created. Let's imagine that you are inputting the code letters into column A and you require the values to appear in column B, and that the table you have created is in rows 1 to 3 of columns K and L. Then enter =VLOOKUP(A1;K$1:L$3;2;0) into B1 and fill that down the column. This function searches the first column of the table at K1:L3 for the value in A1 (or A2 and so on) and returns the corresponding value in the second column. If there is no match, it returns the error "Value Not Available", which appears as "#N/A". I trust this helps. Brian Barker -- 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] [Calc] Keyboard shortcut to delete current row when focus on one of its cells?
At 20:19 21/09/2020 +0100, Dave Howorth wrote: On Mon, 21 Sep 2020 14:13:51 +0100 Brian Barker wrote: At 06:00 21/09/2020 -0700, Gilles Noname wrote: I need a keyboard shortcut to delete the current row. CTRL+- only works when 1) reaching for the mouse and 2) clicking on its ID number on the left-most column (1,2,3, ie. the "grey part") which beats the point of keyboard shortcuts. Ctrl+hyphen, R, Enter? FWIW, on my system, CTRL+hyphen pops up a Delete Cells dialog, R does nothing and Enter causes the dialog to close while executing the first action - Shift cells up. LO 6.2.7.1 That's odd. Figure 36 in the Calc Guide 6.2 shows the Delete cells dialogue with the first letter of "rows" in "Delete entire row(s)" underlined. This means that pressing "R" *should* move the selection to this option. (Figure 16 in Calc Guide 6.4 shows the same.) Brian Barker -- 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] [Calc] Keyboard shortcut to delete current row when focus on one of its cells?
At 06:00 21/09/2020 -0700, Gilles Noname wrote: I need a keyboard shortcut to delete the current row. CTRL+- only works when 1) reaching for the mouse and 2) clicking on its ID number on the left-most column (1,2,3, ie. the "grey part") which beats the point of keyboard shortcuts. Ctrl+hyphen, R, Enter? I trust this helps. Brian Barker -- 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] Calc: how do I?
At 10:29 16/09/2020 -0400, Dan Lewis wrote: I have a dBase database with several tables. How do I open this database in Calc so that each table will have its own sheet? You can do it piecemeal: o Open the database. o Display Tables. o Select a table. o Copy. o Create a spreadsheets with an appropriate number of sheets. o Paste. o Repeat for other tables and other sheets. I trust this helps. Brian Barker -- 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] LO 6.4.6.2 CALC : Appropriate formula?
At 13:50 15/09/2020 +0200, Hylton Conacher wrote: I have eight data values on a single row with corresponding dates next to each value in the format below i.e. A B C D E F G H 25 01/07 blank 60 09/05 blank 30 26/7 In the 2nd row I would like to show the highest number from the first row as well as the corresponding date value in the cell to the right of that value. My A2 formula is MAX(A2,D2) = 60 Shouldn't that be =MAX(A1;D1;G1) ? The issue I am having is to get the corresponding date value from the adjacent column i.e. 09/05 in row B2. How about =OFFSET(A1;0;MATCH(A2;A1:G1;0)) ? Wouldn't you be better off placing your values in three rows of two columns? Then you could use VLOOKUP(). Unfortunately this sort of 'table' VLOOKUP requires is not feasible on my data set. I don't see why not. It might well be that reorganising your data in some way is the right solution. I trust this helps. Brian Barker -- 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] Find all within brackets
At 20:49 10/09/2020 +0100, Séamas Ó Brógáin wrote: Thank you (once again), Brian. I'll test all this in the morning. What I'm trying to achieve is to select all the terms inside the brackets and then in one fell swoop (1) to change them all to italic (and therefore excluding the brackets) and (2) to change the language of the text within the brackets to a different one from the main text. I'll do it all manually if necessary, but there are an awful lot of them! No need. First use \[[^]]*\] and Find All to select all the bracketed phrases. Then apply italics and the change of language. Then search for [|] (meaning open bracket or close bracket) and again Find All to select all brackets. Now undo italics and (if it matters) reset the language for the brackets themselves. I trust this helps. Brian Barker -- 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] Find all within brackets
At 18:42 10/09/2020 +0100, Séamas Ó Brógáin wrote: Some time ago Brian Barker provided a brilliant formula for finding all text between less-than and greater-than signs, namely <[^>]*> I've been trying to adapt it to finding text within brackets [thus], but without success. I know that brackets as such, as distinct from regular expressions, are identified as \[ and \]; so the formula I've come up with is \[[^>]*\] Unfortunately there are two things wrong with this: (1) it finds all text until a later closing bracket (seemingly the last one in the same paragraph) but not necessarily the immediately following one, ... That central greater-than sign also needs to become a close bracket: \[[^]]*\] ... and (2) the selection includes the brackets themselves, whereas I only want the text that they enclose. Am I attempting the impossible? Yes and no. I don't see how you can match something that then doesn't include parts of the pattern. (Others may know better.) But there are things you can do: o If you want to replace what is contained in the brackets but not the brackets themselves, you can reinsert them in the "Replace with" string, as "[something-else]" (no quotes). o You can mark the brackets and their contents separately - using parentheses - and then use the parts in the replacement. Use (\[)[^]]*(\]) and then "$1something-else$2" (no quotes) in the replacement. "$1" refers to the first parenthesised part and "$2" to the second - in this case the brackets themselves. o Another technique would be to match the entire string, including the brackets, do what you want with that, and then perform another Find & Replace to correct the unwanted effect on the brackets. One idea might be to include additional brackets in the replacement, so that the result would be "[[something-else]]". Then it would be a simple task to search for those double brackets and do whatever was required. It's difficult to be more precise without knowing exactly what you are trying to achieve. I trust this helps. Brian Barker -- 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: Generating a Table in LO Base from a LO Calc Spreadsheet
At 11:02 07/08/2020 -0400, Vince Bonly wrote: On 8/4/2020 2:52 PM, Robert Großkopf wrote: The data are part of the database. This is good. But the format of the table is the problem. Open the table and mark the whole content (Click with the mouse left beside the first fieldname). Then right mousclick | Tableformat. You could choose the color for the font there. Best choice would be "automatic" or "black". I am having the same type of problem with another Calc sheet conversion to a Base table. This time, data within each field have an underline present. How do I remove the underscores/underlining? The Font Effects | Text Description | Underling: control box option indicates "(Without)", but after clicking OK button, nothing happens. What am I missing here? As I suggested earlier, some aspects of formatting of some parts of the column headings in a spreadsheet are carried over - perhaps unhelpfully - not into the relevant field names but instead *all* the records copied. My previous solution will work: apply default formatting to the column heading row in your spreadsheet before copying the data. You can easily undo such a change in your spreadsheet if you wish. A workaround may be to use Table Format... to change the Underlining style to something other than "(Without)" and then back again. I trust this helps. Brian Barker -- 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: Generating a Table in LO Base from a LO Calc Spreadsheet
At 13:15 04/08/2020 -0400, Vince Bonly wrote: I am having a problem when I attempt to create a LO Base table from a LO Calc spreadsheet, and I am stumped. This is what I have done: In Windows 10 Home x64, I highlighted & Copied the data, A1:E161, from a LO vers 6.4.4.2 (x64) Calc file to the win10 clipboard. In LO Base-Tables panel, I used Ctl-V to paste the data. Under Tables-Options, selected Definition and data. Check marks placed at "Use first line as column (i.e., field) names", and also at "Create new field as primary key". Primary key named: Passwrd-ID. then clicked the Create button. Table1 appeared in Tables panel. Doing a left mouse click on Table1, I see the field names, but do not see the record data. If I select a field, at the top, the record data is visible only as white text on a dark blue background. Why am I not able to see the record data throughout each field when doing the above described procedure? What am I not doing correctly? This is a pure guess, based on the experience of another user. Are the column titles in your spreadsheet (which become field names) formatted as white text on a coloured background? (And why not?) If so, it may be that the text colour, but not background colour, has been carried over and - unhelpfully - applied to all of the records in addition. If that is the problem, there are two alternative solutions, I think: o Change the spreadsheet formatting (perhaps temporarily) so that the headings are no longer in white text. Create the database table anew. o In the database Tables panel, use Edit | Paste Special... (or right-click | Paste Special...) instead of ordinary Paste and select an alternative option - perhaps "Formatted text [RTF]"? I trust this helps. Brian Barker -- 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] Multiple newlines
At 01:16 21/07/2020 -0400, John Kaufmann wrote: You sent me to do something I should have done before asking the question: examine a hex dump of an ODT content.xml file. I see what you mean about "no codes": A paragraph is just a text string between XML bounds and , and a line break (inside the paragraph bounds) is just . Actually, I don't think they are even that: those are just how they are represented in Open Document Format. Remember that documents files can be saved from LibreOffice is other formats too. I think what you have in the editing window at the end of a paragraph is defined not by how it will be represented in any saved file but by its properties in the window - in other words, what you can do with it and how you do it. And the answer to that is simply a "paragraph break" and a "line break". Brian Barker -- 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] Multiple newlines
At 01:16 21/07/2020 -0400, John Kaufmann wrote: You sent me to do something I should have done before asking the question: ... No criticism intended, of course. First combine single-line paragraphs: [...] AutoCorrect had the effect of changing most non-empty "Default Style" paragraphs to "Text Body" style, with the rest chosen [by spacing hints?] to be "Hanging Indent", "Heading", "Heading 1", "List", "List 1", "Numbering 2" or "Text Body Indent". (Empty paragraphs remained "Default Style".) That was a MUCH more elaborate and sophisticated AutoCorrect than I ever would have imagined. You may or may not want all that to happen. If not, it is wise to use this technique first, before applying any other formatting. You can then choose to select all the text and apply a paragraph style of choice - Default, Text Body, or whatever. Now I understand the point of AutoCorrect Option "Combine single line paragraphs if length greater than 50%". But how do you "adjust the minimum length of such paragraphs in AutoCorrect Options - possibly to 0%"? (The fact that I don't find the setting suggests that I may have also missed something basic in your explanation.) As has already been said, you use the Edit... button - which will be greyed out until you highlight the relevant option. Note: Even after having this excellent explanation on a use of AutoCorrect, I went back to the Writer Guide and still don't find it. In suspect you are right. Then remove empty paragraphs: [...] Again I like your pedagogical approach, matching the action with the reasoning. Good-oh! You should be a teacher. (Er, I was one.) Brian Barker -- 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] Multiple newlines
At 16:38 20/07/2020 -0400, John Kaufmann wrote:
Documents archived in Project Gutenberg are typically simple text,
with each line ending in (Hex:0D0A), so that paragraphs are
separated by an empty line . I thought it would be
simple to convert one such (5657.txt) to format in Writer, ...
It is.
... but stumbled on elementary problems in Find-&-Replace [Ctrl-H]
using regular expressions:
(1) "\n" is not found. Should not "\n" match one of the codes in
? [If not, what code(s) should "\n" match?]
First, once you have your text in a word processor, you do not have
or or or anything else like that in your text;
instead you have *paragraph breaks*. There is no character there,
despite the pilcrow that you can get Writer to display. And what you
are calling "empty lines" are actually empty paragraphs. "\n" in the
"Search for" field matches line breaks, not paragraph breaks. (And
line beaks are line breaks - also no "codes".)
(2) Although "$" is found (matches to ), ...
No, "$" does not match anything; instead, it anchors the expression
before it to the end of a paragraph. So an expression ending with "$"
will match text only if it comes at the end of its paragraph.
... "$$" (for successive occurrences of ) is not found. Why?
"$$" has no sense. If anything it means "this pattern needs to match
something that is *really, really* at the end of a paragraph"!
(3) Doing Find "$" & Replace with " " (single space), is
replaced by " " (single space). However, doing Find "$" & Replace
with "@" (single @char), is replaced by "@@" (double @char). Why?
I don't think that's true. In any case, there are no s present.
To achieve what you want:
First combine single-line paragraphs:
o Apply Default paragraph style to all the text.
o Select all the text.
o Apply AutoCorrect.
(You may need to adjust the minimum length of such paragraphs in
AutoCorrect Options - possibly to 0%.)
Then remove empty paragraphs:
o Search for "^$" (no quotes) and replace with nothing.
("^" anchors your pattern to the start of a paragraph and "$" to the
end. So "^$" matches a paragraph with nothing in it.)
I trust this helps.
Brian Barker
--
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] Compare Two Calc Files in WIN10
At 13:29 18/07/2020 -0400, Vince Bonly wrote: What is your method for comparison of two Calc spreadsheet files? I have two calc files in LO vers. 6.4.4.2 (x64) and want to verify which of the two contain the most accurate data. See "Comparing documents" in Chapter 11, "Sharing and Reviewing Spreadsheets", of the Calc Guide: o Open the edited document that you want to compare with the original document. o Select Edit | Track Changes | Compare Document. o An open document dialog appears. Select the original document and click Open. I trust this helps. Brian Barker -- 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] [Calc] Good way to turn hours+minutes into minutes?
At 15:50 17/07/2020 +0200, Luuk Noname wrote: if A2 = "01h14" Then the formula: =TIMEVALUE(REPLACE(A2;3;1;":"))*24*60 Will return: 74 Basically first replace 'h' for an ':' timevalue returns the minutes since midnight. Except that this fails for time values over twenty-four hours. Brian Barker -- 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] Re: [Calc] Good way to turn hours+minutes into minutes?
At 06:54 17/07/2020 -0700, Gilles Noname wrote: Brian Barker wrote I'm guessing that you have text values. If so, use =LEFT(Xn;2)*60+RIGHT(Xn;2) That was the easiest solution: =LEFT(F3;2)*60+RIGHT(F3;2) Next, copy the cell (formula), select all the cells below, and paste. Or use the "fill handle" to drag down a column. But before, make sure the destination column is also set to Text, like the source column. I don't know why you think this is necessary or desirable. The result of the formula is a number, and this is not changed by formatting the cells as Text. If you want the values left aligned you can arrange that separately. You can anyway format cells containing numbers however you wish. If you really want text values, you can wrap the original formula in the TEXT() function. Brian Barker -- 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] [Calc] Good way to turn hours+minutes into minutes?
At 05:37 17/07/2020 -0700, Gilles Noname wrote: I have a spreadsheet where time is formatted as HHhMM. In the next column, I'd like to show this in minutes. What would be a good way to achieve this? That depends on exactly what you have in your cells. Do you mean that you have text values or that you have numerical values formatted as, say, HH\hMM ? Partly because your image shows the values left aligned, I'm guessing that you have text values. If so, use =LEFT(Xn;2)*60+RIGHT(Xn;2) If there is a risk that cell values might include trailing spaces you could use =LEFT(Xn;2)*60+MID(Xn;4;2) or =LEFT(Xn;2)*60+RIGHT(TRIM(Xn);2) Note that the LEFT() and RIGHT() functions return text values, but these are implicitly converted to numerical values on the fly by the need to apply the "+" operator in the formulae. I trust this helps. Brian Barker -- 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] Inverting a whole column
At 16:11 29/06/2020 -0700, Mark Noname wrote: I have spreadsheets from my financial institutions (for tax purposes). Some of them do the deposits as positive and the debits as negative, and others do the reverse. Is there an easy way to multiply an entire column by -1 so I can make them all fit a uniform convention? Of course. Obvious way: o Suppose your values are in column A, starting at row 2. o In a new column, enter =-A2 in row 2. o Fill down the new column. o Cut or Copy the values in the new column. o Paste back over column A, but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. o In the Paste Special dialogue, ensure that Formulas is *not* ticked. o Delete the new column. Clever way: o Enter -1 in a spare cell somewhere. o Copy that value. o Select the range of values to be negated. o Go to Edit | Paste Special... (or Ctrl+Shift+V). o In the Paste Special dialogue, under Operations, select Multiply. What I've done before is convert the spreadsheets to .csv, use a shell script to invert the chosen column and then convert back to .ods. This is really clunky. There are always unnecessarily complicated ways to solve any problem. I trust this helps. Brian Barker -- 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] remove duplicate rows in calc?
At 10:16 29/06/2020 -0700, Dave Stevens wrote: There's a bug in the storage layout of some data I'm getting from an archive that results in duplicate rows in Calc 6.4, adjacent in all the cases I've seen. Is there a simple way to remove duplicates in this case? Try this: o Select all the material. o Go to Data | Filter > | Standard Filter... . o Change "Field name" to "- none -". o Click Options. o Tick or untick "Range contains column labels" as necessary. o Tick "No duplications". o OK. o If desired, copy filtered material and paste back or elsewhere as desired. I trust this helps. Brian Barker -- 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] Tab stops
At 11:34 15/06/2020 -0400, Dan Lewis wrote: Tools > Options > LibreOffice Writer > Formatting Aids > Tabs You may not have this property checked. But doesn't that control the display of tab *characters* within the text, not the tab *stops* in the ruler that the questioner is having difficulty with? No harm in trying, though ... Brian Barker -- 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] Repeat a footnote
At 14:35 14/06/2020 -0700, John Jason Jordan wrote: I created a footnote to define a term in a document. All went well, except that the term occurs in more than one location on the same page and I want to duplicate the number of the footnote in the text for the other instances of the term, but have the text of the footnote appear only once at the bottom of the page. [...] I [...] decided that I would just put a superscript '1' at the end of the subsequent instances of the term. This works, but I can't get the superscript 1 to look the same as the footnote '1.' The footnote '1' is in some other font and heaven knows what size and position it is in. I can select it, but selecting paragraph or character dialog window doesn't reveal what its settings are. You should expect that: everything worth doing is done via styles! As has already been suggested, what you need is the Footnote Anchor character style. But that isn't quite the whole story. Footnote anchors and footnotes are hyperlinks to each other, so you need only click on the footnote anchor to skip to the associated footnote. Inserting an extra anchor and formatting it appropriately will give the right appearance but not that function. Try this as well: o Insert the new anchor number and format it as above. o Select the anchor number. o Go to Insert | Cross-reference... . o On the Cross-references tab of the Fields dialogue, select Footnotes for Type and the relevant footnote under Selection. o Click Insert and Close. I trust this helps. Brian Barker -- 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] Edit contour not working
At 21:06 10/06/2020 -0700, John Jason Jordan wrote: I have a graphic on a page in Writer. [...] I have aligned it left and set contour to wrap right so that the text will flow down on the right of the graphic. This works, but the text is smashed into the side of the graphic. If you go to the Wrap tab of the Image dialogue (Format | Image | Properties | Wrap or right-click | Properties | Wrap), do you not see Spacing? Increase Right to some suitable positive value. I trust this helps. Brian Barker -- 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] Lost messages [was: Getting a Red-box around Cell entries or Selected Areas]
At 18:08 29/05/2020 +0100, I wrote: ... I suspect that this message from me will also appear in the archive but not be received by *some* mailing list subscribers, whose mail providers simply obey the standards and reject my messages as forwarded by the list. Aha! I'm delighted to find that I was wrong. My btinternet.com mail has been provided for some time by Yahoo, but was changed a few days ago and is no longer. My new mail arrangements no longer show the problem. But M. Gauthier is still out of luck - and it's the fault of the LibreOffice Users list, not his yahoo.com. Brian Barker -- 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] Lost messages [was: Getting a Red-box around Cell entries or Selected Areas]
At 11:20 29/05/2020 -0400, Vince Bonly wrote: Hi Remy: Thank you for your reply, which I found on the Mail-Archive.com. For some unknown reason, I am not receiving emails from [libreoffice-users] via my Thunder Bird email client (imap account). You will receive most messages from the LibreOffice Users mailing list, but not any sent from some domains, including - as M. Gauthier's - from yahoo.com. This is because the administrators of the LibreOffice Users list refuse to make any one of a number of possible configuration changes to the mailing list processor to correct this problem. See https://en.wikipedia.org/wiki/DMARC . Your mail provider is merely following the rules in rejecting some messages. Indeed, I suspect that this message from me will also appear in the archive but not be received by *some* mailing list subscribers, whose mail providers simply obey the standards and reject my messages as forwarded by the list. (So I'm copying this to you privately.) The current situation at the list s tedious and unsupportable. Brian Barker -- 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] Range names
At 09:35 03/05/2020 +0200, Johnny Rosenberg wrote: Let's say I have a spreadsheet (I actually have several ) with a lot of cells and ranges already filled with data and formulas. One day I discover the range naming feature, so I name a few cell ranges. Shouldn't there be some easy way to replace every occurrence of those cell ranges in all my formulas? I don't think any automatic system could do exactly what you probably want. Say your range is A1:B5 on Sheet1 and suppose you name this as Name. You may think that "Name" is now synonymous with "A1:B5", but no: instead it is shorthand for "$Sheet1.$A$1:$B$5". There are thirty-two variations on "Sheet1.A1:B5" you may have in your spreadsheet, each including a different combination of those dollar signs. As you will know, each version behaves differently if you fill ranges from a cell with a formula containing it, or if you copy and paste from such a cell. So the differences are important. By including "Name" in a formula, you are choosing to imply the anchored or absolute version of the range. In order to preserve the precise meaning and behaviour of your existing formulae, any automatic system should replace a spelled-out reference only when it includes all five dollar signs. But in practice one may rarely add all those dollar signs in formulae, instead using only as many are necessary for the filling or copying that one is expecting to need. I suspect you wouldn't be impressed if an automatic system failed to replace "A1:B5" or "A$1:B$5" with your newly defined "Name". But if it did, it would corrupt some spreadsheets that you or others might compose. And when I say easy, I mean easier than doing search and replace on each one of them one by one. I can't find such a feature. Is there an extension for it? When you do this, you would need to determine in each case whether the replacement by the range name would be appropriate. And an automatic system could not do that for you. I'm going to write a macro for it, ... Which of the thirty-two variations will it replace? I trust this helps. Brian Barker -- 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] Splitting columns in Calc
At 02:37 21/04/2020 +0200, Pat Brown wrote:
I have a spreadsheet that includes a column consisting if people's
names. I want to split this into two columns. I want one column with
the given names and the other with their family names. This requires
taking the last word in the column and creating a new column with
this word. Is there a way to do this in Calc?
Here's one way. Suppose the existing names are in column A, starting in A1.
o Go to Tools | Options... | LibreOffice Calc | Calculate | General
Calculations, and ensure "Enable regular expressions in formulas" is ticked.
o If you have anything in columns B and C, insert two new columns, so
you have columns B and C empty.
o In B1, enter =LEFT(A1;SEARCH(" [^ ]+$";A1;1)-1) and fill down the
column as needed.
o In C1, enter =RIGHT(A1;LEN(A1)-SEARCH(" [^ ]+$";A1;1)) and fill
down the column as needed.
o Select columns B and C.
o Copy.
o Paste (back over the same columns), but using Edit | Paste Special
| Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In
the Paste Special dialogue, ensure that Text is ticked but Formulas
is *not* ticked.
o If desired, delete your original column A (so that the new columns
now become columns A and B).
I trust this helps.
Brian Barker
--
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] Calc- find & replace with specific formatting
At 18:23 18/04/2020 -0400, Peter Dutton wrote: In Calc I want to find a specific word and replace that word with a bold version of it. I think this depends on whether the word you refer to is the total contents of a cell or just part of it. Formatting in a spreadsheet is generally handled on a cell basis, not by character. So although you can indeed manually set part of the contents of a cell to, say, bold, I don't think there is going to be any way to apply this automatically. And I don't see any way to achieve what you want as local formatting, even if the word is the entire contents of a cell. But there is a workaround: o Create a cell style including the Bold attribute. o Select an appropriate cell range - perhaps even an entire sheet or sheets. o Use Conditional Formatting to apply the Bold cell style to any cell whose contents are exactly your word. I trust this helps. Brian Barker -- 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] Re-flow text in Writer
At 18:00 16/04/2020 -0500, Jason Noname wrote: Is there a way to force text to reflow in Writer? Text reflows automatically. Say I have a file with hard wraps at the end of each line and I want to reflow all the paragraphs. You mean that you want to *join* existing paragraphs, so that the entire (selected) text becomes a single paragraph? o Search for $ . o Replace with nothing. You will need to have "Regular expressions" ticked, of course. This will do exactly what you ask, so that paragraphs are joined without anything in between. In practice, you may wish to replace with a single space instead of nothing. Note that if you have empty paragraphs in your text, these will be removed - but they will prevent paragraphs preceding and following them from being merged. You could merely repeat the same Find & Replace, but that will duplicate the spaces, if you have included them. Instead, first replace $ with space and then replace $ with nothing. Or alternately is there a way to find and replace line breaks (searching for '\n' does not seem to work)? This is not an alternative to your other question but a different requirement. If you indeed have line breaks, \n will match them. But paragraph breaks are not line breaks, and \n will not match paragraph breaks. I trust this helps. Brian Barker -- 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] Open .csv by specifying column widths on command line
At 09:18 07/04/2020 +, Nobody Noname wrote: I've got some csv files to work with. They contain fields which are quite long, and opening them with LO results in very large columns, which are difficult to handle. Setting the column width by hand also has me enabling line wrapping to show the full cell content. I'm looking for a way to open those files by specifying my intended column width and by enabling line wrapping. I think there is a workaround, which may or may not be helpful. o Start a fresh spreadsheet. o Adjust the column widths as desired. o Format relevant cells to "Wrap text automatically". o Save as a template. To use: o Open CSV fie normally. o Select all (Edit | Select All, or Ctrl+A, or click the rectangle at top left where the row and column headers meet) or relevant cell range. o Copy. o Open new spreadsheet based on template. o Click cell A1 or select all (as above). o Paste, but using Edit | Paste Special (or Ctrl+Shift+V) instead of normal Paste. In the Paste Special dialogue, ensure that Formats is *not* ticked. I trust this helps. Brian Barker -- 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] Re: Bouncing messages from users@global.libreoffice.org
At 13:47 06/04/2020 +0900, Howard Barr wrote: On Fri, 3 Apr 2020, 09:01 , wrote: Hi, this is the Mlmmj program managing the mailing list. Some messages to you could not be delivered. If you're seeing this message it means things are back to normal, and it's merely for your information. Here is the list of the bounced messages: - 55854 - 55855 This happens a lot and always from the same users. I have added these users to my contact info but that hasn't helped. I guess it's my side as others are obviously getting them. What else can I try? You wrote to me privately about this (from a different address) nearly two years ago. It's all down, I think to DMARC; see https://en.wikipedia.org/wiki/DMARC . This is a well-intentioned scheme to allow mail providers to reject incoming messages if they originate from a mail server which is not a registered server for the domain in the From: header. Such messages will generally be spoofed and thus spam, so this is a Good Thing. But a problem arises if mailing list processors (quite sensibly) preserve the original From: address on distributed messages - as does the LibreOffice Users list processor. The distributed message claims to be from the original sender (which it is, of course), but is sent via the LibreOffice mail server, which gives it the appearance of being a spoof message. You will fail to receive such messages if both (1) the originating mail domain publishes a list of approved servers and requires receiving systems to reject messages from elsewhere, and (2) your mail provider chooses to play ball and obey this requirement. It must be that your mail provider satisfies condition (2), and the messages you will fail to receive are those whose mail domains satisfy condition (1). (Mine does this, so I'm having to send you a private copy of this message: you will presumably not receive the copy distributed via the list. I do not receive copies of my own contributions. The bounced messages above may well have been from me. Some others will ) There are solutions to this problem, the simplest of which is for the list processor to append something such as ".INVALID" to the From: address in relevant messages, so that they are no longer recognised as coming from a DMARC-conformant domain. Many mailing list providers do this. I wrote to about this as long ago as 1 May 2018, but my suggestion for action was rejected, on the ground that "none of the workarounds [are] satisfactory for everyone". Thus the problem continues. The present arrangement is unsupportable. You or others could start a campaign to have it put right. Brian Barker -- 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] Adding a / (forward slash)
At 07:50 04/04/2020 -0400, Peter Dutton wrote: Thanks- the "/" works perfectly. Good-oh! Nothing's easy in this world. Many things are. What has been created in the calc sheet is the day number of the year which is followed by the "/". In the cell beside the result is the remaining number of days in the year. Here's an example of what I'd like to see for this date (February 10, 2020) 41 / 325 In this case 325 is the remaining number of days in the year 2020 from the date Feb. 10. The cell in which the formula used to obtain the figure of 325 is- =365-S4+1 "S4" is the cell where the day number of the year is located returned by the formula, as mentioned below- =DATEDIF($Begin_Here.$E$76,R4,"d")" / " I still don't think this is the clearest or best formula for what you need. (And you've lost the ampersand, though I suspect that's a "feature" of your mail system.) What happens to the remaining days number in cell S4 the dreaded error - #VALUE! is returned. I suspect this has something to do with the formatting of the cell but can't figure it out. It's nothing to do with formatting: it's to do with, er, values. It's hardly surprising, since - as I made clear - what you have now put in S4 is not the number 41 but the *string* "41 / ", and that is not a number. You cannot calculate with strings (unless they happen to represent numbers in a simple way). What do you expect if you try to divide "three" by "two"? "one point five"?! Any ideas? Yes. Take the concatenated slash off your formula so that it creates the number 41 in S4, as before. Then use =S4&" / "&366-S4 for your result. I trust this helps. Brian Barker -- 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] Adding a / (forward slash)
At 20:24 03/04/2020 -0400, Peter Dutton wrote:
Here's a formula I'm using
=DATEDIF($Begin_Here.$E$76,R4,"d")
The above formula returns the day number of the year where
$Begin_Here.$E$76 [...] the date of 12/31/19
Cell R4 has the date 10 (which is Monday, February 10, 2020)
"10" is not a date - unless you mean the date that is internally
stored as the number 10, which would be 9 January 1900! And that
would be an error for the function, since the end date needs to be
later than the start date.
"d" is the interval
Well, it's the unit in which you want the returned interval specified.
Wouldn't it be easier to use
=DAYS("2020-02-10";$Begin_Here.$E$76)
or just
="2020-02-10"-$Begin_Here.$E$76
?
Even more easily, abandon your "Begin_Here" value and try (with your
10 February 2020 date in R4)
=R4-DATE(YEAR(R4)-1;12;31)
This will produce the number 41 - providing the result cell is
appropriately formatted.
It would be nice to have a / (forward slash) after the day number of
the year which is returned by the above formula. How can this be done?
You can concatenate strings using the "&" operator, so just put &"/"
after any of these formulae, such as
=R4-DATE(YEAR(R4)-1;12;31)&"/"
The numerical value 41 is implicitly converted to a string and
concatenated with the slash to create the *string* 41/ .
I trust this helps.
Brian Barker
--
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] cannot find and replace paragraph break in writer
At 17:34 26/03/2020 -0400, Udvarias Ur wrote: I have a similar problem. I sometimes, especially when copying and pasting text from a WEB page, find 2 hard returns one after another. How can I search for double hard returns, ¶¶, and replace them with single hard returns, ¶. Don't think of searching for what you are calling a "hard return", because it doesn't really exist. That pilcrow is just a marker, showing where there is a paragraph break; there really is nothing there! If you have two consecutive paragraph breaks, what you have between them is an empty paragraph - and it is this that you need to delete. Just search for ^$ (caret - dollar) and replace with nothing. I trust this helps. Brian Barker -- 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] cannot find and replace paragraph break in writer
At 19:01 26/03/2020 +0100, Procuste Piziocampte wrote: In a writer document I would like to find paragraph breaks (A paragraph break that can be entered with the Enter or Return key) with a tab so following the instructions ... . I should perform a find/replace: in find \n and in replace \t or if it doesn't works in find \n and in replace \n and then in find \n and in replace \t checking the regular expression box (only that one) but it doesn't work, writer gives me the "search key not found" That is because in the search box \n finds *line* breaks (created with Shift+Enter), not paragraph breaks. how can I solve? If I understand you correctly, try this. First: o Search for (.)$ (left parenthesis - dot - right parenthesis - dollar). o Replace with $1\t (dollar - one - backslash - lowercase tee). The search matches any single character at the end of a paragraph. The replace string replaces whatever that character is and adds a tab character. Then: o Search for $ (dollar). o Replace with nothing. There may be more elegant solutions ... I trust this helps. Brian Barker -- 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 case of first letter of every paragraph
At 18:28 05/03/2020 +1100, Keith Bates wrote: I have been using the voice typing feature in Google Chrome to write some documents, then download them in .odt format for editing. I find that it consistently starts a new paragraph with a lower case first letter. So I need to find the first letter of every paragraph in the document and convert it to upper case. I thought of using Find and Replace with Regular Expressions, which is easy. ^[:lower:] finds every lower case letter which is the first letter of a paragraph. But when I enter [:upper:] in the replace box it simply pastes the text [:upper:] . I have both the "match case" and "regular expressions" boxes ticked. Is there a way to do this other than going through the document and replacing manually? At 20:00 05/03/2020 +1100, Keith Bates wrote: To answer my own question: 1. Use the Find and Replace box and Reg. Exp.. Click on "Find All". 2. Close "Find and Replace" dialog. 3. Right click on one of the highlighted letters. Format- Text- Upper case Does this also work? o Find: ^. (caret-dot) o Replace: & (ampersand) o Tick "Regular expressions". (No need for "Match case".) o With the cursor in the Replace box, click Format... . o On the Font Effects tab, for Effects select Capitals. o Replace All. o Voilà! Brian Barker -- 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] Re : Bouncing messages from users@global.libreoffice.org
At 11:42 31/12/2019 +0100, M Henri Day wrote: Den tis 31 dec. 2019 kl 05:00 skrev : Hi, this is the Mlmmj program managing the mailing list. Some messages to you could not be delivered. If you're seeing this message it means things are back to normal, and it's merely for your information. Here is the list of the bounced messages: - 55635 From time to time I receive messages of the type above, but never any information about what I am expected - or allowed - to do with the <> which accompanies them. Can anyone on the forum enlighten me ? At 12:54 31/12/2019 +0200, Hylton Conacher wrote: I also had the same message and note that it was for the same message number that was "missed". As I am sure we are not using the same ISP in South Africa, so who knows maybe the mailing list admins picked it up as spam. ANYONE know? I think I do. The list help text says "Anyone can retrieve message number N from the list's archive by sending a message to (change the N to the number of the desired message)". But if I'm right, any repeat copy would also fail to be delivered to you. It's all down, I think, to DMARC; see https://en.wikipedia.org/wiki/DMARC . This is a well-intentioned scheme to allow mail providers to reject incoming messages if they originate from a mail server which is not a registered server for the domain in the From: header. Such messages will generally be spoofed and thus spam, so this is a Good Thing. But a problem arises if mailing list processors (quite sensibly) preserve the original From: address on forwarded messages - as does the LibreOffice Users list processor. The forwarded message claims to be from the original sender (which it is, of course), but is injected into the LibreOffice mail server, which gives it the appearance of being a spoof message. You will fail to receive such messages if both (1) the originating mail domain publishes a list of approved servers and requires receiving systems to reject messages from elsewhere, and (2) your mail provider chooses to play ball and obey this requirement. My mail domain (which is Yahoo in disguise) satisfies condition (1), so you will fail to receive my messages to the list if your mail provider satisfies condition (2). I do not receive copies of my own contributions. Message 55635 is, I think, my contribution to the thread "full date string to date"; you can see this at http://document-foundation-mail-archive.969070.n3.nabble.com/full-date-string-to-date-td4271380.html . If I'm right, you will not receive copies of this message via the list, which is why I'm copying it to you both directly. There are solutions to this problem, the simplest of which is for the list processor to append something such as ".INVALID" to the From: address in relevant messages, so that they are no longer recognised as coming from a DMARC-conformant domain. Many mailing list providers do this. I wrote to about this as long ago as 1 May 2018, but my suggestion for action was rejected, on the ground that "none of the workarounds [are] satisfactory for everyone". Thus the problem continues ... Brian Barker -- 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] full date string to date
At 14:39 30/12/2019 -0500, James Lockie wrote: I had a spreadsheet with dates that I think somehow got converted to text. I tried pasting it as plain text with detecting special numbers and I tried setting the column to a date format but it still seems to come out as text. 'Wed, Jan 2, 2020' Dates are sensitive to language and locale, so no guarantees, but ...; you should be able to convert your data using the spreadsheet program's own facilities. Try this: o Suppose your date data is in column A. Select the range (or column) and go to Data | Text to Columns... . Under Separator options, tick Comma, Space, and Merge delimiters. OK. You now have the four parts of your dates separately in columns A, B, C, and D. o In the first row of your data in a new column, enter (for, say, row 1) =DATEVALUE(C1) - and fill down the column. Note the jumbled order of the parameters, so what is offered to the DATEVALUE() function is three parts of your date concatenated as "2Jan2020". o Format the values in the new column as desired, perhaps as NN, MMM D, You can copy the resulting values back over the originals if you wish - or elsewhere, of course - using Paste Special with Formulae unticked. Oh, and by the way, unless something very strange is about to happen, I'm expecting 2 January 2020 to be a Thursday, not a Wednesday! I trust this helps. Brian Barker -- 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] Re: Calc: suppress header/footer from page 15 onwards
At 18:02 25/12/2019 -0700, David S. Crampton wrote: I expect that the technique of creating a Style for each sheet will work. You'll pardon my giggling at "expect". This is the solution given on 26 May 2011 earlier in the thread you reused and indeed in the documentation: "Headers and footers are assigned to a page style. You can define more than one page style for a spreadsheet and assign different page styles to different sheets." So yes: it does work. As it was this afternoon I didn't have the moxo to work through the Edit Styles dialogs. Your workaround was intelligent, of course, but probably took you longer than discovering how to create and apply separate page styles. Do get used to styles, as they are a powerful facility which repays the time spent learning about them (more so in text documents). Note that, in this case, you could use the "New Style from Selection" button in the Styles and Formatting dialogue to create a duplicate of your existing page style; you can then modify one style to create the different footers that you require. Next month I will be asked to again update and publish this contact list. I will work with page styles first. Good-oh! Brian Barker -- 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] Re: Calc: suppress header/footer from page 15 onwards
At 14:36 25/12/2019 -0700, David S. Crampton wrote: Here it is 9 years later. I think the answer is much as was given in 2011! I just encountered a need to have Sheet1 use Footer1 (Sheet1 prints in 3 pages). Then Sheet2 use Footer2 (another 3 pages). With focus on either Sheet1 or Sheet2 to set 'Format | Page | Footer ... ' changes the Footer to be the same in both Sheets. I worked around it but clumsy and error prone. (You really ought to tell anyone hoping to help you what workaround you have found and consider error-prone.) Headers and footer are properties of page styles. If you have the same page style for both of your sheets, changing the footer of that page style will obviously affect the pages produced by both sheets. All you need to do is to use a different page style for each of your two sheets (probably creating a suitable new page style yourself) and set appropriate footers in each. The only difference I see between the behaviour with spreadsheets (Calc) rather than text (Writer) documents is that in text documents you can insert manual page breaks wherever you wish, whereas in spreadsheets you cannot insert them within sheets but an automatic possibility of a break of style occurs between every sheet. I trust this helps. Brian Barker -- 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 - Date - increase number times...
At 17:35 12/12/2019 +1300, David Noname wrote: Thank you for your prompt reply, Brian. It is much appreciated No probs! Perhaps I didn't explain myself clearly enough. In simple terms, I want to know whether there is any way that when I enter a new date in Column D it will automatically increase the current value in Column B by 1. I guessed that you meant something like this - but no, it wasn't clearly stated. If you could achieve what you ask, the result would be hopelessly fragile. We all make mistakes. Suppose that you enter your new date incorrectly. The value in column B will be incremented. Then you need to enter the correct date, whereupon the value in column B will be incremented again. How would you then correct the situation? If you tried to correct the value in column B manually, you would overwrite whatever formula there was doing the trick for you in the first place - and disable it. And what if you entered a correct new date but mistakenly for the wrong person? There is, I think, a solution, and that is to separate data entry from the display you actually want to see. Construct a separate area - quite possibly on a separate sheet of the spreadsheet - for your data entry. This will have a row or column for each of your named "reactors", and you will add dates here instead of in the results sheet directly. Note that you *add* dates, so that you assemble a list of dates, rather than overwriting a single entry with the most recent date. Now all you need to do in column B of your display table is to construct a formula which counts the number of dates currently listed for that person in the data entry area. And in column D of the display table you will similarly construct a formula which picks up the most recent date from the data entry area. With this scheme, any mistaken entries can be corrected with the display table always picking up the corrected values. I trust this helps. Brian Barker -- 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 - Date - increase number times...
At 17:49 11/12/2019 +1300, David Noname wrote: I have a spreadsheet in Calc ... It has Columns ... B - No, formatted number general ... Is there a formula that I can enter in Column B which will increase incrementally increase the figure by 1, please? I think you are saying that you want values in column B to be one greater than they are. That's a contradiction. If the value in Bn is four, you want it to be five. But that means it's no longer four. And if it's now five, how would any formula know you do not want it to be six? And so on? I trust this helps. Brian Barker -- 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] Strange issue with NOW() that i have not encountered before
At 10:53 01/11/2019 +1300, Steve Edmonds wrote: On 01/11/2019 09:21, Tim Deaton wrote: On 10/30/2019 5:11 PM, Brian Barker wrote: At 20:02 30/10/2019 +, Pete Nikolic wrote: I am having an issue with NOW() . Unfortunately for you, NOW() means "now"; it may be that you need a THEN() function! I am using =IF(ISBLANK(A3),"", IF( ISNUMBER(B3), B3, NOW() ) ) I have copied this by dragging the corner of the cell to 100 other cells the problem is when i enter data into a cell in column 3 ... Er, column A, perhaps? ... the time changes in every cell in column 3 and it should not. You clearly don't want it to, but since NOW() means "now", it should. By default, each time you make changes to a spreadsheet, formulae are recalculated, and since all your formulae contain a reference to NOW(), that will be updated to the current date and time, not the one at which previous changes were made. [...] This reply confuses me. The OP is using NOW() as the final option in a nested IF() statement. So, to my understanding, NOW() should not be executed unless both of the first two options fail. Otherwise, it appears that the mere presence of the NOW() statement as one of the options causes the rest of the IF() statement to be rendered null and void. To me, that behavior makes it a bug. We have not seen some contents of columns A and B from the new sheet and the old sheet to try to replicate the issue, maybe it is a bug or maybe the formula is doing what it should be. We haven't - but we don't need to. If any row has the date and time in the formulae column, it must be that whatever is in columns A and B in that row means that both IF() conditions failed. When the questioner makes changes in other rows (the problem situation), there is no change to existing rows and both IF() conditions will again fail in those rows. So the recalculation will mean that cells in relevant rows will execute the NOW() function again and (unhelpfully for him) bring the date and time up to date. Brian Barker -- 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
