# Tagged Questions

**1**

vote

**1**answer

21 views

### How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape.
This can be done in Excel, but in OpenOffice when i set ...

**1**

vote

**2**answers

58 views

### How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...

**3**

votes

**2**answers

46 views

### Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC).
Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded ...

**1**

vote

**1**answer

119 views

### Changing the units on the x-axis in a LibreOffice Calc chart

I've used the LibreOffice chart wizard to make a chart, and I would like the x-values to be different to the default of 1,2,3... (in this case, 500,1000,...). How can I do this?

**1**

vote

**1**answer

50 views

### LibreOffice calc: How to apply multiple tables to a single chart

I'll say it right off the bat - I am a complete Excel/Calc amateur.
What I want to do is apply 3 tables, each with 2 columns of data into a chart with 1 XY-line for each.
My current data looks ...

**1**

vote

**2**answers

261 views

### Interpolate values between two cells

I've got a spreadsheet that looks like this in Libre Office Calc:
+-----------+
| | A |
| 1 | 444 |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| ...

**-1**

votes

**1**answer

35 views

### How to add the current page number in a cell of a calc sheet

I would like to add the current page number in a cell of that page and to refer back this cell for this page number in a cell somewhere in the same sheet / in another sheet. Is there a way to do so?

**-1**

votes

**1**answer

106 views

### Change background fill of cells in LibreOffice worksheet containing specific text

How can I change the background fill of all the cells of a LibreOffice spreadsheet worksheet that contain a specified text?
In Excel that could be done by configuring the settings in the find and ...

**1**

vote

**1**answer

127 views

### Applying Conditional Formatting to all computed values in LibreOffice Calc

In LibreOffice Calc, how can I use the Conditional Formatting feature to apply a style to all cells which contain a computed value (via formula, as opposed to a directly input value)?
LibreOffice ...

**1**

vote

**0**answers

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

**0**

votes

**0**answers

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

**1**

vote

**1**answer

205 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

**1**answer

123 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

**1**answer

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

**1**answer

72 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

**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

644 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

**1**answer

69 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

**1**answer

184 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

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

**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

**1**answer

396 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

**1**answer

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

**7**

votes

**2**answers

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

333 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

**1**answer

446 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

**1**answer

174 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

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

**1**answer

361 views

### Open source alternative for working with large xlsx files [closed]

I am new and I hope the this is the right place for the following question:
I would like to collaborate with people using large xlsx files (many rows).
Which open source alternative can handle these ...

**0**

votes

**1**answer

299 views

### Where is openoffice.org add-on located after installed (Ubuntu)?

I installed a tool which now appears in Openoffice Calc under Tools->Add-ons.
I thought that this tool would appear in ~/.openoffice.org/3/user directory, but I cannot find it.
I am especially ...

**-1**

votes

**1**answer

867 views

### How to make LibreOffice Calc to connect points in a holed series chart with lines?

I've got a time series data table with one function (row) value available for every month and the second for every 3rd month (every quarter), having holes (empty cell values) for every 2nd and 1st ...

**3**

votes

**3**answers

2k views

### Finding min/max values in column where cell matches something

I have a spreadsheet laid out kind of like this:
Date Project#
2012-01-01 130
2012-01-02 153
2012-01-03 153
2012-01-04 130
2012-01-05 130
2012-01-06 130
And I want to get ...

**3**

votes

**2**answers

979 views

### Excel/Calc/Whatever: Flip my axes - ie reflect my table across the diagonal

Lets say I have a table and I want it flipped across the diagonal. So instead of X by Y its Y by X...
Before:
A B C
RED 1 2 3
BLUE 4 5 6
...

**1**

vote

**1**answer

220 views

### Delete cell content in Libre (Open) Office based on the cell value

I have a huge csv file (tens of thousands of rows) that I need to filter based on different criteria. After trying to find a proper CSV editor, I decided to use LibreOffice Calc. CSVed is great, but ...

**1**

vote

**1**answer

3k views

### LibreOffice pivot table group by month

Im having data like this:
Date Customer Amount Month
09-01-12 A 20 =A2 (formatted so it shows month name)
10-01-12 B 10 =A3 (formatted so it shows month name)
...

**6**

votes

**3**answers

4k views

### How do I get a chart in LibreOffice Calc to have regular time intervals when the data is not regular?

I have an area chart in LibreOffice Calc where I would like the X axis to be measured in one month intervals spaced evenly apart.
The data going into the graph, however, is not regularly spaced at ...

**0**

votes

**1**answer

283 views

### How do I get numbers before and after a colon within a cell in Calc?

I have some cells which contain times entered in by users, which are written in the following format: mm:ss. There are never any hours. The seconds always have leading zeroes. The minutes may or may ...

**1**

vote

**1**answer

2k views

### Can two rows be reordered into one column in OpenOffice/LibreOffice Calc?

Is it possible in Open/LibreOffice Calc to take the values from a set of two or more rows, and display them in one column? In order words, make a separate column which derives its values from the ...

**1**

vote

**1**answer

77 views

### Can I use two cells in the same column as a Y axis in Libre/Open Office?

I have data arranged as following:
What I'd like to do is create a graph that looks something like the following (which I generated with graphics software):
So, for example, to generate the ...

**1**

vote

**1**answer

848 views

### Is it possible to create a chart in OpenOffice calc using columns and rows?

Is it possible in OpenOffice Calc to create a single chart where one set of data is derived from columns, and another set of data is derived from rows?

**0**

votes

**1**answer

160 views

### Math function: What's the “this line” placeholder?

When I want to multiply a field I can say =J9*K9.
But what if I want this over a selection of rows? I've selected Line 6-25 and for all I want to say =J{THIS_LINE_NUMBER}*K{THIS_LINE_NUMBER}*.
How ...

**1**

vote

**1**answer

863 views

### Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...

**1**

vote

**1**answer

247 views

### Rearrange cells in a Open Office spreadsheet, from data in cols to data in rows

I have this spreadsheet (Open Office) and all the data is filled in so they form a col,
and I would like to change this so they fill a row instead.
Kind of mirror the doc in both axis.
This is how ...

**2**

votes

**1**answer

327 views

### Are there nonlinear optimization implementation for OpenOffice Calc?

Are there nonlinear optimization implementation for OpenOffice Calc?
I found one: http://extensions.services.openoffice.org/project/NLPSolver . But the methods provided there are evolutionary ...

**1**

vote

**1**answer

3k views

### How do I create a line graph which ignores zero values?

I want to create a graph showing trends over time, calculated from a formula of various data.
However, some data is missing, resulting in zeroes in the calculated data which the chart uses.
When ...

**7**

votes

**3**answers

3k views

### Permanently and globally stop openoffice calc from autocorrecting dates/time

When I type "15:45" or "21/12" openoffice Calc changes it to "15:45:00" and "21-12-2011".
How do I disable this globally and permanently?
I know you can use "Format cell..." and then select text, but ...

**8**

votes

**4**answers

8k views

### How to set the default font in LibreOffice Calc?

How do you set the default font used in new documents of LibreOffice Calc? I am using LibreOffice 3.3.2.
Some answers below have been updated for LibreOffice 4 as well.

**3**

votes

**2**answers

4k views

### Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle ...

**3**

votes

**1**answer

1k views

### Open Source Spreadsheet with Sensitivity Analysis

Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...