Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

3
votes
1answer
143 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 ...
3
votes
1answer
221 views

Cut and transpose in LibreOffice

When using cut and pasting LibreOffice maintains exact formula used along with moving old references to point to the new location, however when using paste special: transpose it treats the past as if ...
3
votes
1answer
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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
111 views

Tagging or defining automatically in an Excel or Libre office spreadsheet?

I want to review and sort my expenditures in a spreadsheet where I have all my transactions listed. Is there a way to automatically label or tag to categorize the transactions like in a budget ...
2
votes
1answer
749 views

How to make a LibreOffice Calc XY (scatter) plot with named dots?

I have a simple XY scatter plot made from a 2-column spreadsheet. When I hover over a dot, I can see the ID of the dot (e.g. "Data point 8456"). I would like to know if I could have the content of a ...
2
votes
1answer
785 views

Changing the cell range of conditional formatting in LibreOffice Calc

Is there any way of changing the range of conditional formatting in LibreOffice Calc (version 3.6)? I have a set applied already to a range of cells, but there is no obvious way of changing which ...
2
votes
1answer
650 views

LibreOffice Calc: Formatting Time is adding ' char

I have some data in calc like so: 05:00:27 01:27:02 07:29:57 06:22:01 I select the cells, and right click 'format cells', and choose Time -> 876613:37:46. When I do this, it adds an apostrophe ...
2
votes
1answer
387 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 ...
2
votes
1answer
277 views

how to get Libre Office Calc to tell me when there are invalid characters

I have a spreadsheet in XLSX format that was generated by a python program. it turns out that the python program had left some invalid characters in the file. When I open the file in Excel, it at ...
1
vote
1answer
17 views

Applying reference to another sheet to all cells

I have LibreOffice Calc file which has 2 sheets. I wanted to reference Sheet1 from Sheet2, so I wrote function =Sheet1.A1 in Sheet2 cell A1. How do I apply this to all cells on Sheet2? (Sheet2.B5 = ...
1
vote
1answer
428 views

How to compare numbers in columns

I have data in libreoffice like ColumnA ColumnB 504231 504109 504109 504201 504201 504299 like so much data Then I want output like ColumnA ColumnB 504231 504109 504109 504201 504201 ...
1
vote
1answer
836 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
1answer
622 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 ...
1
vote
1answer
387 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
1answer
2k views

Strip text from number in Libre Office Calc

I want to convert the string in column C into number. I want to convert "11,00 TL" to 11. How can do this?
3
votes
0answers
166 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
73 views

How to customize default CSV format in LibreOffice?

Every time I export my data to CSV (and I do it often) I have tick the "Edit filter settings" checkbox, choose UTF-8 for the encoding, and semicolon for the fields separator manually. I do this ...
2
votes
0answers
1k 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 ...
2
votes
0answers
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 ...
2
votes
0answers
2k 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 ...
2
votes
0answers
634 views

LibreOffice Calc, how to filter rows based on interval

I would like to filter a spreadsheet on LibreOffice 3.4.4 Calc, based on values of a second spreadsheet column. How can I do that ? I would like intersection and, if possible, complement filters. So ...
1
vote
0answers
6 views

How to reference cells by name in Libreoffice 4.2

In the Libreoffice version that came with Ubuntu 12.04, I could select a column, name it, and then construct a formula that referenced this name. e.g. Say I had two columns A=Price and B=Quantity. If ...
1
vote
0answers
70 views

Equivalent for FILTER() formula in Libre Office

In a Google Spreadsheet, I create a report sheet by entering a formula like this in the top left cell. =FILTER(Sheet1!A2:E, Sheet1!E2:E > 0) This gives me all the rows in the Sheet1 from row ...
1
vote
0answers
41 views

Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes. In another place I need an array of product codes for a specific vendor. The ...
1
vote
0answers
150 views

updating links in LibreOffice Calc xlsxfile results in err:509

I have just started to use Libreoffice so I am totally new to the system and immediately encountered a problem with a calc document. The document has originally been made in Excel (but on a Mac) and ...
1
vote
0answers
161 views

Why is libre office calc inserting a ' at the beginning of the cell?

This is the most ridiculous thing i've encountered.. I've imported a csv, I end up with nice neat clean rows of numbers, no formatting, nothing weird, no " or ' left over, just numbers. When I go to ...
1
vote
0answers
123 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
0answers
302 views

How to force LibreOffice Calc to treat time values as numbers

I want to do something very simple in LibreOffice Calc: I have a set of simple CSV files that contain lists of durations, formatted as HH:MM:SS. I need to make a SUM of those lists. With some ...
1
vote
0answers
188 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 ...
1
vote
0answers
431 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
0answers
163 views

Formatting text in a LibreOffice Calc cell

Currently I'm working on the layout of a calendar for the new year 2013. I found a template that was designed in LibreOffice Calc. How can I layout the text in the cell of a Calc program? What can I ...
1
vote
0answers
473 views

Copy formula across row incrementing sheet number, down column incrementing number by 2

I have information on four different sheets I would like to copy to this summary sheet. I want to know how do I copy the formula across the row and down the column on this summary sheet(say this is ...
1
vote
0answers
100 views

Extracting “tag1, tag2” values from a column in LibreOffice-calc

I've got a LibreOffice calc spreadsheet with columns containing some freeform text, followed by a column with "tags" which I've entered (coded interview data). For example: 1234,"some explanation ...
0
votes
0answers
19 views

Concatenate a reversed range in Libre Office

I'm kind of new with programming, but I have to make an excel document, that I can use interactively because the output of one of my programs doesn't fit with the input of the others... I have lot of ...
0
votes
0answers
23 views

Disable spell checking/auto correct in Libre Office Calc for a specific file?

Is it possible to disable spell checking and/or auto correct in the Libre Office Calc application on a file by file basis? Normally in calc I want spell checking turned on, but for some files it just ...
0
votes
0answers
27 views

Create a pie chart from amount/category pairs

From a table like Amount | Category 2 A 1 B 5 A how to create a pie chart with one sector per category found in the second colum and the summed up values for each ...
0
votes
0answers
16 views

Position cells within view and copy functions/formulas from existing cells in Libre Calc?

I would like to keep a specific range of rows visible in Libre Calc. As new rows of data are entered, I would like the sheet/page to scroll, keeping the current row visible. As a comment made me ...
0
votes
0answers
35 views

LibreOffice calc; is it possible to automatically make cells coloured according to the HTML code entered into them?

I am creating a LibreOffice calc spreadsheet designed to be basically be a customized version of the table entitled "Default element colors, by atomic number:" on this page ...
0
votes
0answers
20 views

Show deleted cell values (Track changes in Libreoffice Calc)

I was looking for this feature for a while now: If I turn tracking the changes on and delete the value in a cell it just highlights the border around the cell in red, meaning it has changed. Is it ...
0
votes
0answers
35 views

How to swap two columns in specific rows in LibreOffice Calc?

I have a few hundred contacts stored in Google Contacts, and I would like to sort them. I exported them in CSV and opened them with LibreOffice Calc. Some of my contacts have first and last name ...
0
votes
0answers
16 views

assign string “tags” based on numeric interval in Libreoffice Calc

I'm struggling with an easy issue. I'm working in Libreoffice Calc and I have a numeric column with values from 0 to 365. What I would like to do is to create another column assigning string values ...
0
votes
0answers
31 views

Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D. I am aware of OpenOffice: get ...
0
votes
0answers
37 views

Libre Office Calc - how to suppress search results window when performing a macro search?

I work with a macro that finds and replaces certain characters in a spreadsheet. I use this macro a lot and I don't need the "search results" window to pop up after every search since I am not ...
0
votes
0answers
60 views

How can the background of a cell in LibreOffice Calc be set to a hex value contained another cell?

How can I make a cell in LibreOffice Calc have a background colour equal to a hex value specified in another cell? For example, if cell A1 has the value e51c23, how can I set the background of cell ...
0
votes
0answers
9 views

How to create readable libre office expressions

When an expression gets longer, it's nice to format it on multiple lines, much like we do for source code. In Libre office Calc, new line can be be entered in cell using Ctrl+Enter. But once you ...
0
votes
0answers
43 views

LibreOffice calc: Can I create a yearly sum out of monthly sheets?

As a simplified example, let's say I have the expenses of a small company in one file per year, and every file has 12 sheets (one per month). The sheets have these columns: date|department|amount ...
0
votes
0answers
96 views

Is it possible to recover cells with hidden content wiped out upon opening with LibreOffice

Some of my .xls Microsoft Excel files contain cells with hidden information formatted using custom code ;;; to deter casual viewing. I made the switch to LibreOffice a year ago and converted all the ...
0
votes
0answers
72 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
0answers
370 views

How to fill in formulas automatically in LibreOffice Calc?

How can I get LibreOffice Calc to automatically fill in formulas from the current row into the next row? I have a spreadsheet for keeping track of my personal finances and I'd like to just press ...