OpenOffice.org's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively

learn more… | top users | synonyms

5
votes
1answer
299 views

Default font and cell padding when opening files in LibreOffice/OpenOffice Calc

I've seen solutions for setting a default font when creating new files. What about a default font (and cellpadding) when opening an existing CSV file? LibreOffice seems to always default to Liberation ...
2
votes
0answers
215 views

Disable prompt when opening a CSV file in LibreOffice/OpenOffice Calc

Every time I open a CSV file, LibreOffice presents me with a prompt asking to confirm Character Set, Language, and Separator options (tab, comma, semicolon etc). This prompt is not present in ...
2
votes
1answer
1k views

LibreOffice pivot: How to count 1 per line?

I have a list of fruit occurrences like this: banana banana apple orange apple And I want LibreOffice to give me a number of occurrences per fruit, for instance: banana | 2 apple | 2 orange | 1 ...
0
votes
0answers
230 views

OpenOffice Calc with csv file produces Err:520

I'm loading a csv file into OpenOffice Calc (version 4.0.1) and in only 1 column I get 'Err:520' as the cell value. I checked under Tools->Options->OpenOffice Calc->View->Display to check whether ...
0
votes
1answer
560 views

Libre Calc error on a float in decimal notation. Err. 519 (#value error)

There is a problem with my libreoffice. I doesn't calculate cells with decimal points, possibly seeing them as text. I have checked my locale settings (you can see them in the picture above) that ...
0
votes
1answer
556 views

How do I sum across a list of columns defined in an extra column or cell in excel

Suppose columns A, B and C contain numbers. I want to sum everything in any of these columns if they are listed in an extra column or cell. The columns to be summed can be anywhere in the spreadsheet. ...
5
votes
1answer
731 views

Is it possible to remove the hyperlink background in OO Calc?

Hyperlinks in OpenOffice Calc are displayed with a solid background which covers the background color of the cell. I don't want a solid background; I want the background color of the cell to show ...
6
votes
1answer
9k views

How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...
0
votes
1answer
103 views

Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...
0
votes
1answer
129 views

Macro to manage sport ranking and calendar?

I need to write a macro (or a different easy way) to manage ranking and calendar for a curling tournament. The event will follow the Shenkel system: first match determined by general draw after ...
4
votes
2answers
3k views

Move rows with the keyboard in Calc

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this. Note that I am not looking to ...
7
votes
1answer
2k views

Preventing LibreOffice from using smart quotes (“ ”) instead of (dumb) straight quotes?

I use LibreOffice Calc to make lists of programs with different parameters to run on an hpc cluster. For each batch of commands I must do a find and replace on the smart quotes, as they cause errors ...
0
votes
1answer
95 views

Conditonal sum in Excel

I would like to write the formula (in OpenOffice, but I imagine it won't be any different than Excel) to perform the following calculation: -> Go through the values in column B from B1 to B30 and add ...
1
vote
0answers
238 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
1
vote
0answers
449 views

OpenOffice Basic Change BackGround Color for Range Based on Text in Column 0

I'm trying to test each cell in column 0 "A" to see if it has specific text "Budget" "Living" "Savings" and if it does then change the row background color to a specific color for each. I am able to ...
0
votes
1answer
226 views

Open Office Calc truncates my data on paste, how do I fix this?

Just as the title says, I am using Open Office Calc as a flat file to upload data. One column is my description and therefore can be a bit lengthy. Unfortunately it truncates this text. Anyway to ...
1
vote
1answer
172 views

Is there another way to display a spreadsheet within OpenOffice Writer besides an OLE object?

I'm writing a paper for college in which I would like to either display or link to a large spreadsheet. I'm using OpenOffice Writer and Calc. I've tried importing the spreadsheet as an OLE object, ...
1
vote
1answer
629 views

Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc? To clarify, can I set a cell's formula to something like this: =B3*C4-system("/my/amazing/script ...
4
votes
1answer
720 views

Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value A1:"AB 0123" and in cell B1 I need the same value with a line break instead of the space like B1:"AB\n0123". How can I archive this? I ...
3
votes
0answers
3k views

Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with the font color red. All other rows are in the default font color. Can I use the font color of a cell within a formula to react to ...
0
votes
1answer
1k views

Openoffice Calc - Quickly count marked lines when filter is active

I need help counting lines. When I set a filter (AutoFilter) in Calc, rows that are filtered are hidden, but all rows keep their row number. How can I see quickly, how many lines are currently ...
0
votes
1answer
163 views

How do I generate a column of dates.

I want to generate a column of dates that are staggered by one month like this: 7/01/13 8/01/13 9/01/13 Right now what I've tried is dragging down the black dot in the lower right hand corner of ...
1
vote
2answers
920 views

How do I drag down complex calculations in open office.

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks ...
1
vote
1answer
146 views

How does Open Office Calc make this conversion?

In a column, I have dates of the format 2013-05-27. When I selected "Default Formatting", the dates got changed to five digits, for example, 41422. Where does this number come from? It's not even ...
0
votes
0answers
301 views

enteries in column are automatically changed to upper case letters

I am very new with Open Office and spread sheets in general. In a column whenever I add a new entry it changes all the letters to upper case. Is there a way to disable this? If not, many entries ...
-1
votes
1answer
401 views

OpenOffice-Calc advanced filter

In OpenOffice-Calc, using the advanced filter, how can I compare one column to another? As an example, I need to filter for items that have less than a 30 days supply. Using the column name Test, my ...
1
vote
0answers
46 views

How to make just one text column be unquoted in final CSV?

I'm using OO calc and i have one column with phone numbers that have leading 0s, which are vital for the number to be valid. I can format the cell as text but i do not want the numbers to be quoted ...
0
votes
1answer
156 views

Why is Openoffice Calc automatically rounding the sum of two sums?

I have two columns which have been summed. When I sum the two cells with those results, openoffice rounds the sum every time. If I only enter two numbers and then sum them, it operates normally. ...
3
votes
1answer
1k views

Autocomplete only on demand in OpenOffice Calc

In Open-/Libreoffice Calc, it is possible to enable the auto-completion of cells. That means the cell is filled e.g. with a string that starts with the characters already typed. This may give unwanted ...
1
vote
2answers
1k views

How can I use extremely large numbers in my spreadsheet programs?

If I try to calculate large exponents in LibreOffice Calc or OpenOffice.org Calc, the results come out to be erroneous. See these two examples where I calculate 13^18, Using LibreOffice Calc Using ...
0
votes
1answer
306 views

Pasting data in openoffice Calc

I want to paste data in Openoffice Calc but it doesn't seem to work. The example with the first row (of 124 rows), this is the data in a .dat-file: 0.592 0.349 1.333 0.617 When I want ...
1
vote
1answer
1k views

OpenOffice Calc - How to substitute multiple characters?

I need to substitute non-English characters in French words to the closest English charachers. For example: "Combien ça coûte" Becomes "Combien ca coute" I also need to remove some punctuation. Is ...
28
votes
2answers
43k views

How to color rows based on cell value in Open/Libre Office

How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't ...
0
votes
2answers
586 views

Open/LibreOffice Calc data “extend selection” stops at blank rows

I have a spreadsheet and have right clicked on a column header then selected Sort from menu. The dialog box provides an option to extend the selection, which I accept. The problem is that the ...
2
votes
1answer
814 views

Find the Nth instance of a text string within a range?

I have a list of names next to a list of shifts. I want to collate a list of people on each shift by day, which will automatically and dynamically update. I can't work out the best way to do it. In ...
1
vote
1answer
182 views

Database workflow advice? [closed]

I've got a product database that I need to update a couple of times a week and am looking for some advice on what workflow I should setup. I can export my database from my online store as a CSV, I ...
2
votes
1answer
952 views

LibreOffice Calc - apply style to neighbouring cells

I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data. Here is ...
1
vote
0answers
226 views

How to disable command+scroll zooming in openoffice calc

Is there anyway to have command+scroll in OSX do nothing instead of scrolling? I press the keyboard shortcut all the time and it throws my focus off. I have looked extensively in forums, but often the ...
2
votes
1answer
869 views

Getting date of last change as a cell value in Open Office Calc

I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key ...
2
votes
2answers
205 views

OpenOffice Calc regex returns wrong value - what's wrong with my regex?

I have a column of text with values concatenated by my bank, as in a typical monthly statement. Here is an example (column A240): OCT 26 BALANCEPROTECTOR PREMIUM $10.05 I'm trying to set the ...
0
votes
3answers
5k views

How to change the hh:mm:ss format in spreadsheets

Currently, I have a data set which is "mm:ss" and needs to stay / be calculated that way. The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" and to add ...
2
votes
0answers
568 views

OpenOffice Calc: Cell Background/Border Color

I am trying to create a spreadsheet with colored cell backgrounds. But I encountered this strange bug, where the background of a cell is bigger than the shown border, so coloring a cell will actually ...
0
votes
1answer
168 views

How to format a date in Calc?

When I enter 09/02/2013 00:00:00 in a cell in Windows 7, the cell displays 02.09.2013 00:00. I have specified the proper Format code in Format Cells: In Windows XP, the outcome is as expected. The ...
0
votes
1answer
37 views

Looking for suggestions on how to arrange 7900+ lines of text in Calc

I have a dataset with the complete text of the Greek Novum Testamentum (New Testament); see an image here. Each line is one verse.  I will find the book and chapter boundaries with the help of these ...
1
vote
1answer
143 views

How is the LOOKUP function named in German? [closed]

The english version of OpenOffice Calc does have the function LOOKUP. How is this function named in German (Deutsch)?
2
votes
1answer
7k views

Libreoffice calc relative and absolute cell reference on copy-paste

I'd like to create a simple Calc sheet, where column A contains the days, B contains how many hours of work i had done and C the price for that day. The price is simply a multiplication of hours (B ...
1
vote
1answer
1k views

Binary spreadsheets with bitwise operators [closed]

I am looking for a way to easily construct binary spreadsheets using bitwise operators such as SHIFT, AND, OR and XOR. I've looked at Gnumeric but can't find any function that'd allow you to set the ...
0
votes
2answers
127 views

OpenOffice.org Lowercases second capital letter

How can I turn of the feature in OpenOffice.org Calc which converts CCleaner to Ccleaner?
0
votes
1answer
230 views

Split sheet by rule (filter) in Open Office

I want to split a large sheet. I have different products and for each of them I would like a new file. Is it possible to split the sheet by some rule? P.S. I am new in here, so if my question is ...
0
votes
1answer
84 views

Get frequency distribution of several columns partitioned by one other column

I'm doing some basic survey analysis. I want to break down responses to various questions based on Male or Female. Each row is a response, each column is a question. Column A has the Male/Female ...