Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

3
votes
1answer
109 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
198 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
110 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
640 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
728 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
357 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
252 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
322 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
618 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
493 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
257 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
1k 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 ...
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?
1
vote
1answer
239 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
1answer
587 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 ...
1
vote
1answer
482 views

Any way to make interactive graphs in LibreOffice like Google Doc's Trend chart?

Google Docs has a nice trend viewer that can be created just like any other chart: Is there any way to get an interactive graphic like this in OpenOffice? Manually adjusting the axes is really ...
0
votes
1answer
16 views

Generating unique values in Libre Office Calc

I've got a list of entries in a range in LO Calc. The max size of the range is 1 X 9, but may include blanks and/or duplicates. How can I extract a list of unique values automatically, so that my user ...
2
votes
0answers
59 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
129 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
248 views

Open only the first 10 lines of large CSV file in LibreOffice Calc

I will start receiving many large CSV files, of which only the first few lines are of interest to me. I was hoping to open them by piping only the first few lines to Calc, but this doesn't work: $ ...
2
votes
0answers
600 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
587 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
41 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
105 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
101 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
188 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
159 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
334 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
158 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
431 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
99 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
22 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
11 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
22 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
20 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
38 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
32 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 ...
0
votes
0answers
95 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 ...
0
votes
0answers
8 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
31 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
58 views

LibreOffice Calc dynamic text sort

Given the following table: A B 1 AA AA 2 HH BB 3 BB HH 4 II II 5 UU LL 6 LL UU I need to sort the values of column A to B dynamically, using only ...
0
votes
0answers
20 views

Hide rows that have a zero value

I've got large spreadsheet with over 4000 rows. In column G I have a sum. If that sum is 0 I want to hide the entire row. Conditional formatting doesn't seem to do the job. Is it possible to hide ...
0
votes
0answers
76 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
104 views

How do I unformat unused rows in libreoffice calc columns to reduce loading time/memory use in PHPExcel

I have just started using PHPExcel to try to read data for application development. I have found that a few of my libreoffice calc 3.4 spreadsheets can be read quickly while most take a very long ...
0
votes
0answers
62 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 ...
0
votes
0answers
69 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
343 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 ...