Spreadsheet component of the LibreOffice suite

**0**

votes

**0**answers

52 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 curling tournament.
The event will follow the Shenkel system
first match determined by general draw
after that ...

**1**

vote

**2**answers

516 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 ...

**0**

votes

**0**answers

93 views

### How to get the intersection line between two .csv file?

How to get the intersection line between two .csv file?
My question seem quite difficult to understand but i will give you an example like this:
I have 2 .csv files:
+---+-------+----+ ...

**0**

votes

**1**answer

325 views

### Currency conversion in LibreOffice Calc?

I have a *ods.-spreadsheet in LibreOffice. Some fields contain numbers in currency format (JPY), but they could also be in general number format. I want to create corresponding fields which display ...

**3**

votes

**0**answers

83 views

### How do I conditionally format a cell that contains a dropdown list

Hi Guys I want to know how to conditionally format a cell that contains a drop down list of 3 values. I want each value to represent a different coloured background when selected in the drop down ...

**1**

vote

**1**answer

66 views

### How can i reset my Libre Office Calc setting

I am using Libre Office Calc for my Excel work.
Image 1:
Image 2
I can not see the format color in on Libre office installed @My System.
but on my Colleague System who uses the same Libre ...

**0**

votes

**0**answers

51 views

### Libre Office Calc vs MS Excel EDIT-GO-SPECIAL-BLANKS

I need to know what works in the place of MS Excel's EDIT-GO-SPECIAL-BLANKS.
I need to fill in blank cells in a column that has different cell values in cells and several blanks below each cell.

**0**

votes

**1**answer

342 views

### Column order in stacked bar chart is the the opposite from spreadheet

Often I have a spreadsheet in LibreOffice Calc like this:
Apple 423412 124233
Banana 234234 23434
Cherry 23324 423432
I then make a stacked bar chart. However the order of the stacked bar chart is ...

**0**

votes

**0**answers

160 views

### How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...

**1**

vote

**0**answers

131 views

### LO Calc: Why are data labels placement options greyed out?

When creating pie charts I like to add the values of the individual slices to the chart. LO Calc allows you to do this ('Add data labels') By default Calc places these values in the center of the ...

**3**

votes

**1**answer

431 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**0**

votes

**1**answer

67 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 ...

**2**

votes

**0**answers

30 views

### Libreoffice calc: pagebreak within row?

When printing a LibreOffice Table,
LibreOffice will automaticaly place the pagebreaks so that they appear after/before a row.
When having many tall rows this results in a heavy paper-usage. How can I ...

**0**

votes

**1**answer

112 views

### DateTime to just Date

In LibreOffice, Calc under Linux, I have a table with DateTime values (eg. 2011-06-15T15:37:56+00:00). How shall I convert these values to just Date values (eg. 2011-06-15)? I suspect it is very ...

**3**

votes

**3**answers

162 views

### How do I convert a delimited list to a multicolumn table in excel?

I have a list like this:
Friut;Taste;Color;Other;Apple;Good;Red and Blue;1;Orange;Really Good;Orange;12
And I want to convert it by selecting every 4 delimitations and turning them into rows like ...

**1**

vote

**1**answer

157 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 ...

**3**

votes

**1**answer

131 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 ...

**1**

vote

**1**answer

67 views

### Following Sunday based on date in LibreOffice Calc [duplicate]

I'm looking to create a calculation to display the following Sunday in day-month format based on the current date (=NOW()).
How can I do that?

**0**

votes

**0**answers

103 views

### Table auto-formatting templates for libreoffice calc

Are there any good auto-formatting templates for tables for Calc? The default ones are all very ugly compared to Microsoft Excel.
Also is there a way to automatically extend the table formatting when ...

**1**

vote

**5**answers

996 views

### How to convert text to numbers for all cells in a spreadsheet?

I have a spreadsheet containing values that are entered as text by having an apostrophe ' in front. I tried the search and replace function, but for some reason it couldn't find those cells containing ...

**0**

votes

**1**answer

213 views

### How to change all cells in a spreadsheet to just one font type and size?

I just receive a spreadsheet that was converted from a PDF file. Due to the poor conversion, the cells contain a mixture of fonts and font sizes. For example in:
Future Insurance
The first ...

**2**

votes

**0**answers

1k 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 ...

**1**

vote

**4**answers

2k views

### How to add month to date in LibreOffice calc

This seems like an obvious question.
I have a date column, and I want to create a formula to increase it by one month for each column.
10/2013, 11/2013, 12/2013, 1/2014, 2/2014, ...
How do you add ...

**1**

vote

**0**answers

226 views

### Libre Office Calc, User-defined Number format permille

I know that LibreOffice allows you to define custom number formats.
I have a spreadsheet that displays some smallish percentages (all are less than 1%).
I would like to use permille ‰ instead.
...

**1**

vote

**1**answer

351 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 ...

**0**

votes

**1**answer

2k views

### How to do a “great circle” calculation in MS Excel or LibreOffice?

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called ...

**2**

votes

**2**answers

276 views

### LibreOffice: stop automatic formatting of text into a formula?

In LibreOffice, I'm entering some text into cells, which happens to look like a math problem:
-5 / -10 / -15
The program assumes I want to calculate this, and sticks an equals sign automatically in ...

**1**

vote

**1**answer

303 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 ...

**0**

votes

**1**answer

533 views

### How remove all characters after pattern in LibreOffice Calc

In LibreOffice Calc how can I use Regular Expressions to find and replace every character after a pattern, and remove the pattern itself.
Example:
123_2015970_etgn.jpg
124_2015971_etgn.png
...

**2**

votes

**2**answers

6k views

### Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data.
Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...

**7**

votes

**2**answers

8k 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

**2**answers

316 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 ...

**3**

votes

**2**answers

276 views

### Does libreoffice have a math concept like 'infinity'?

Does libreoffice calc have special numbers corresponding to -infinity and infinity similar to python's numpy?
They would obey rules like:
x = some number
infinity + x = infinity
-infinity + x = ...

**-1**

votes

**1**answer

97 views

### How to check if a date is in the future

I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...

**2**

votes

**3**answers

2k views

### libreoffice what is a fast way to paste special > text and numbers only (aka paste special values)?

I copy and then paste values in excel quite a bit (when I use Excel). When I use LibreOffice you can do the same thing (see related question here) but it is oh so tedious!
It gets especially tedious ...

**0**

votes

**1**answer

109 views

### Attempting to open TSV file in Libreoffice Calc results in being opened by Writer

I have been working with TSV files for months, but I have one file that refuses to be opened in LibreOffice Calc. It instead brings it up in writer. This behavior is consistent across command line and ...

**2**

votes

**1**answer

408 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 ...

**0**

votes

**0**answers

61 views

### Libreoffice won't let me have a read-only ComboBox

I have a database table with some entries. I want to display all the fields of this table as a ComboBox. I want the user to be able to select this any entry in this ComboBox without modifying the ...

**0**

votes

**2**answers

575 views

### Calculate conditional average on disjoint cells

I would like to know if libreoffice calc has the functionality to calculate an conditional average on disjoint cells. An example would be: Calculate the average for every second cell in Row1 if Row2 ...

**1**

vote

**1**answer

154 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 ...

**1**

vote

**1**answer

980 views

### how to get Libre Office to adjust row height to accommodate text entry?

I need for LibreOffice to automatically adjust row height for text entries.
I have tried to acomplish this by checking the wrap text option, which I thought would be the solution, but it is not.
How ...

**0**

votes

**3**answers

523 views

### Sum up to N highest-value out of a series?

I have a spreadsheet where each row is a set of numerical competition scores. Each row can be thought of as a single competitor in the overall competition, and each column is a competition event. ...

**0**

votes

**1**answer

865 views

### Auto fit column value without increasing row height in LibreOffice

I m using Libre Office Calc on my Ubuntu 12.10 desktop and I need a help.
I am writing some data from other resource and save it for each row. I have only 2 columns which values are lengthy and rest ...

**0**

votes

**2**answers

951 views

### How do I mark weekends red in LibreOffice Calc?

I have a list with dates in it, and I would like to mark the dates representing weekends as red (for easier telling which day of the week it is without having to manually look up the day of the week ...

**3**

votes

**6**answers

3k views

### Quickly fill blank cells in a list in LibreOffice Calc

For example:
Cell A1 = 2/3/2012
A2 is empty
A3 is empty
A4 = 3/7/2012
A5 is empty
A6 is empty
A7 is empty
What I want to do is:
select the ...

**0**

votes

**1**answer

61 views

### Using LibreOffice and having problems, copying details

A staff member in our office is having trouble pasting details from a spreadsheet {In any format} to a template in .ott format.
When pasting and details from the spreadsheet, it removes some of the ...

**2**

votes

**2**answers

2k views

### How do I create a sum for each group of data in Libre Office Calc?

I have a simple spreadsheet like this:
Invoice_Number Invoice_Date Invoice_Amt
1000 1/1/12 1
1001 1/1/12 2
1002 1/2/12 ...

**1**

vote

**2**answers

881 views

### Is it possible to display a range of cells in a different sheet in LibreOffice calc?

I've been looking around for a while, but could not find a answer.
My budget sheet contains a meta-sheet called "settings". In "settings" there is an area e.g. C10:D20 that I would like to display in ...

**1**

vote

**1**answer

2k views

### Grouping date-level data by month to produce averages in Excel?

How would I return a monthly average with below cells without manually selecting each month separately?
A B
1 2013-02-13 25600
2 2013-02-14 5000
3 2013-03-15 12300

**1**

vote

**1**answer

682 views

### Lookup the nearest value in a column?

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D.
So, if A is 160 I want to ...