Ask Your Question

kumiponi's profile - activity

2018-06-16 17:03:29 +0200 received badge  Nice Answer (source)
2015-09-11 21:46:08 +0200 received badge  Famous Question (source)
2015-09-11 21:46:08 +0200 received badge  Notable Question (source)
2015-06-22 18:18:16 +0200 commented answer LibreOffice Calc calculation error

MOD() is particularly misleading because it's supposed to be all about integers. Let's test if 20 is a prime number: MOD(FACT(20-1)+1;20) = 0. Hey, apparently it is...

2014-09-07 09:32:50 +0200 received badge  Nice Answer (source)
2014-03-19 20:48:34 +0200 commented answer custom function not automatic updating

An ugly hack but works, thanks. MS Office has a proper way to do this, but sadly LO appears to lack it.

2014-03-18 13:38:44 +0200 commented answer Set cell background color based on adjacent cell value

Yes, think of the "formula" as a condition in an IF statement. If it returns boolean TRUE, then the formatting applies. "A1" in my example has to be adjusted if the first cell in the conditional formatting range isn't B1; the references in the formula are appropriate to the first cell in the range and are implicitly adjusted for the rest.

If you want, you can also embed "conditional formatting" in the cell formulas themselves with the STYLE and CURRENT functions.

2014-03-18 13:38:44 +0200 received badge  Commentator
2014-03-17 21:09:03 +0200 answered a question Set cell background color based on adjacent cell value

Yes it is! When you create a conditional formatting rule for column B, select "Formula is" as the condition, and type A1="some string" as the formula (in the adjacent text box). Then select the style you want to apply.

2014-03-17 18:30:10 +0200 commented question Why can't I save my new spreadsheet?

Also, what is the exact error message and where are you trying to save it. Try selecting another folder.

2014-03-17 18:22:17 +0200 commented question I've done something wrong. I have a spreadsheet or database and all the sudden data-sort is greyed out or inactive. How can I fix this?

Hi, it's hard to tell without having the actual document at hand and knowing your LibreOffice version and operating system. You could also be more specific about what you've done.

2014-03-17 01:58:26 +0200 received badge  Autobiographer
2014-03-16 13:05:15 +0200 answered a question Calc How do you copy, then paste columns to another sheet preserving widths?

Depending on the situation, the easiest way may be to first copy the first sheet (right-click on the tab, select Move/Copy Sheet). Then add in the parts that differ. Column width and row height are not cell properties, so if you copy a region, it will not copy the heights and widths, but if you instead copy whole columns or rows (select them by clicking the labels) the widths OR heights are carried over. If you'll be making a lot of the forms, you could create a template for it.

To copy just the widths or heights (and not the contents of the cells), copy the columns or rows, use Edit > Paste Special, and select only Formats.

2014-03-15 23:03:30 +0200 received badge  Popular Question (source)
2014-03-15 21:50:14 +0200 answered a question Saving documents with Dragon

If there exists a free light-weight API for integrating Dragon into applications, it's possible that someone may look into it. Accessibility is always an important issue, IMHO. But until then, this is not really a LibreOffice issue but a Dragon issue. That being said, if someone has workarounds or solutions, please share them.

2014-03-14 22:42:36 +0200 answered a question How do I switch the X and Y axes in a Calc Chart?

See the above comment for the answer, it seems to be correct.

2014-03-14 14:23:02 +0200 commented question How to remove popup?

Consider using a Linux operating system. They run LibreOffice and Firefox very well and have much less viruses.

2014-03-14 01:24:23 +0200 answered a question Ignoring borders when using conditional formatting.

Hi, it's totally possible to ignore borders. When you create a style, you normally base if off another style ("Default" by default). It's in the "Linked with" selection box in the Cell Style dialog box (where you edit the style). Below that is a "Contains" text field that shows what changes your style has from the base style. If you haven't set any borders for your style, then a cell having that style can have any custom borders you want. The conditional formatting won't apply borders either if the style it applies doesn't contain any border values.

2014-03-14 01:01:07 +0200 commented answer How do I count how many text entries in column A match the entry in column B

Extra tip: if you want more powerful tools to manipulate that third column I mentioned, you may want to learn about array functions.

2014-03-14 00:52:21 +0200 answered a question How do I count how many text entries in column A match the entry in column B

I understood that the asker has two columns and wants to find how many rows have matching entries, between those columns. If column A contains, say, potato, turnip, and carrot, and column B contains potato, broccoli, and carrot, then you'd want the value 2.

The normal thing to do in this situation is to create a third column that has a comparison formula on every row, and then count the matches. You first enter =IF(A1=B1;1;0) into C1. Then you select the cells you need in column C and use Edit > Fill > Down. This copies the formula and changes it to suit every row. Then you put something like =SUM(C1:C100) somewhere to count how many rows match. You can hide the third column if you think it looks messy.

Another way to do this, without the extra column, would be to write a macro function. Depending on how versatile you'd want the function to be, it could be quite short or tens of lines long.

2014-03-12 03:29:20 +0200 commented question How can I adjust line spacing for cells with word wrap in Calc ?

It appears to be possible to embed fonts into documents. See File > Properties > Font.

2014-03-12 03:26:48 +0200 received badge  Editor (source)
2014-03-12 03:26:17 +0200 answered a question How can I adjust line spacing for cells with word wrap in Calc ?

The line spacing toolbar buttons are there but they're not functional. If you try to paste 1.5-spaced lines from Writer, the spacing is changed to 1. It's however possible to draw clumsy, ugly text boxes that do have paragraph formatting options. You could also write each line in its own cell and set the background of those cells to white (or some other colour, instead of "No Fill") so as to hide the grid lines and make them appear like a single cell. I'm not aware of any real solution, besides adding the feature to Calc.

2014-03-12 01:16:22 +0200 commented question Changing colors in options not working

It's hard to understand if you don't describe the exact steps that produce the error. Every detail can matter. For example: 1) Open a new Calc document 2) Open Tools > Options etc.

Also note that even if you change the colour of comments, it won't change the colour of existing comments in documents that aren't open at that time, because every comment has a background colour value that can be manipulated individually and is saved with the document.

2014-03-12 00:45:19 +0200 received badge  Enthusiast
2014-03-11 23:31:10 +0200 answered a question Breakpoints ignored in User-defined function

I'm not sure if I understood the question correctly, but it seems to me that the natural way would be to write another Basic procedure that would call the desired function with whatever arguments you want to test and Print the output. Then run that procedure from the Basic window.

If you need to use Calc functions in the arguments, it's easiest to first find out the calculated values of the arguments in Calc, and then use those values in the function call in Basic.

2014-03-11 21:23:18 +0200 commented answer Printing and print preview in black & white only

There is also Tools > Options > LibreOffice > Print > Convert colors to grayscale. This, however, does not affect the Page Preview. Notice the term "Page Preview" instead of "Print Preview" (in some versions of MS Office). It would be a decent feature request to ask for the preview to more closely resemble the actual printout, like in the little preview in the print dialog. Disclaimer: I haven't tested the colour converting feature with an actual printer.

2014-03-11 02:16:36 +0200 answered a question can a macro call itself

Here's an example function that calls itself – it's recursive.

Function factorial (n As Integer) As Double
    If n < 0 Then
        Print "Error!"
    ElseIf n < 2 Then
        factorial = 1
        factorial = n * factorial(n - 1)
    End If
End Function
2014-03-06 12:30:00 +0200 received badge  Supporter (source)
2014-03-06 00:01:40 +0200 received badge  Necromancer (source)
2014-03-06 00:01:40 +0200 received badge  Teacher (source)
2014-03-05 23:04:05 +0200 answered a question How do I regenerate a sheet after removeRange to workaround bug 61109?

This bug has been fixed in recent release versions according to the bug report linked.

2014-03-05 17:21:48 +0200 commented answer Please find me easy way convert date string into data

Please post only answers as answers. If you want to ask something from the asker, do so in comments. This way we know which questions have been answered. :)

2014-03-05 17:19:09 +0200 answered a question Please find me easy way convert date string into data

You can use the functions DATEVALUE and TIMEVALUE. DATEVALUE ignores time information and TIMEVALUE ignores date information, so you may have to add them together to get a specific point in time.

For example: =DATEVALUE("2014-05-03")

Normally, if you paste in a time or date string that Calc understands, it is automatically converted into a number and formatted as a time or date. The above mentioned functions don't cause this automatic formatting so you may want to select the appropriate number format for the cell.

The underlying format for times and dates is a floating-point number. The integer part is the date and the fractional part is the time. If you want to convert such a number into a string in Calc, use the function TEXT,

For example: =TEXT(A1; "YYYY-MM-DD")

2014-03-04 18:47:51 +0200 commented answer Create attributes for each cell in a column

I know this question is a year old, but I thought it still deserved an answer.

2014-03-04 18:46:33 +0200 answered a question Create attributes for each cell in a column

LibreOffice is essentially a WYSIWYG editor and ODF is just a storage format. There is no functionality to directly or indirectly edit the source code of the document, as far as I know. When LibreOffice saves a document, it constructs a file from the document's representation in memory, presumably as efficiently as possible. The file you get may differ significantly from the memory structure.

When you write a program to read a spreadsheet file, you do a similar thing – you reconstruct the table into memory. Presumably the first row you read contains column labels. After that, you can add those labels as attributes to cells in the consecutive rows if you want, or create some other kind of memory structure that suits your needs.

Another way to differentiate and categorize cells would be to create custom styles for them. This way the data table wouldn't have to be ordered by column labels.

2014-03-04 16:49:53 +0200 asked a question How does gotoOffset work in LO Basic/Calc?

Hi, I'm not sure if this is a bug so I'll ask here first. Sample code:

Sub PrintCursorLocation (cursor As Object)
    print cursor.getRangeAddress.StartColumn & ", " & cursor.getRangeAddress.StartRow
End Sub

Sub CursorTest
    Dim cursor As Object
    cursor = ThisComponent.CurrentController.ActiveSheet.createCursor()
    cursor.gotoOffset(2, 2)
End Sub

GotoOffset doesn't seem to be doing anything. There is no error message either. According to the Open Office developer manual, it should move the cursor to the expected cell (C3 in this case) relative to it's current location. GotoNext and others do move it. Am I doing something wrong?

I'm using LibreOffice release version on Windows Vista.