Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

1
vote
1answer
59 views

How to add a Table of Contents to a LibreOffice Calc file?

Adding a ToC in LO Writer is no problem, but how to insert a ToC in a ods file? For a workbook with tables longer than one page to be distributed as printout (not as file), it would be nice to have a ...
1
vote
2answers
19 views

Merge / glue exported pdf from spreadsheet application

Spreadsheet applications offer to export tables as pdf. Often, the table is to wide to fit on a single page (without scaling), so several pdf pages need to cover the full width of the table. Let us ...
0
votes
2answers
62 views

Extracting named groups of regular expression in LibreOffice Calc

In Python 3, it is quite easy to extract text from named groups as shown in the following example: import re myStr = r"4000/2000/5000/7000" reObj = re.compile(r"""(?P<g1>\d+) # a capturing ...
0
votes
1answer
9 views

Libreoffice Calc XY scatter graph - How do I add minor gridlines?

The option is greyed out under Format > Grid. I have looked here and here but when I change Format>Axis>Y axis>Scale and set it off automatic and change the minor interval count, minor gridlines still ...
0
votes
0answers
15 views

Some Conditional Formats 'break' after i reopen my sheets

I'm using LibreOffice Calc here and have noticed that once i reopen my sheets, some of my Conditional Formats stop working and have #REF in them. It's an easy fix, all I have to do is remove a couple ...
0
votes
1answer
560 views

Weird glitch after copying from a CSV file?

I was doing a programming job that involved writing data to a large CSV file (700 MB). I occasionally opened the file using libre office to check the data coming in. This morning I opened the file ...
1
vote
1answer
23 views

SUMIF criteria string with spaces?

me again;\, how do i get the criteria part of a SUMIF formula to recognize a string that has spaces? example (sorry for poor formatting, i have no idea how to format more spaces:(): A B Tim ...
1
vote
1answer
22 views

How to perform Conditional Formatting on the max value of a range of cells?

i'm assuming what i'm trying to accomplish here is really simple, so bear with me here. i just want to create a Conditional Format on a row of cells and have it highlight (bold'ed) the maximum value, ...
0
votes
0answers
11 views

LibreOffice Calc 5.1.1.: Fill does not appear in pull down Edit menu

In LibreOffice Calc 5.1.1.: Fill does not appear in pull down Edit menu when I have 2 or more cells highlighted. How do I fix this?
0
votes
1answer
8 views

How to get a list of all named ranges in calc

I want to list out all the named ranges and the data ranges of a Calc workbook containing several sheets. I am jus tmoving from excel to calc and thus not too familiar with functions/macros in calc.
3
votes
1answer
32 views

Is there a way to add and/or logic into the criteria field?

Using LibreOffice Calc with the expression SUMIF, is there a way to make the criteria field look for 2 types of values using OR logic? Or do I have to make 2 separate SUMIF's and just add them up? A ...
3
votes
1answer
16 views

How to sum the values of one column depending on the value of another?

with LibreOffice Calc I'm trying to set something up so it sums the values of column D IF the corresponding row in column A contains certain words. So far I know sumif works, but how do I set it up so ...
0
votes
0answers
13 views

Libreoffice Calc - how to drag cell leaving formula in original cell?

Is there a way/setting to leave the formatting in a specific cell after using 'cut' or dragging the content of the cell away? 'Protecting' the cell is not what I'm looking for since that disables ...
1
vote
3answers
17 views

How to convert plain text files into libreoffice cal (Excel sheet) on Ubuntu 14.04?

I have a plain text file containing a huge amount of data. The data's are separated by semicolon and a newline for starting a new line of the following data. Here's a sample: ...
2
votes
2answers
36 views

Identify cell containing given text in LibreOffice Calc

Is there a function in LO Calc that will find a cell with the given text? The search and find functions appear to give a location within a string. That is, if I search for the text "a" within two ...
0
votes
1answer
57 views

How to use VBS to manage LibreOffice Calc files?

I am learning VBS scripts and someone gave me a code below, that works with Microsoft Excel. How to convert it to work with LibreOffice Calc? Dim ObjExcel Call ExcelSetup("Sheet1") Sub ...
1
vote
1answer
15 views

Fill right in open/libreoffice

I'm using Libreoffice calc for an application where it's necessary form me to fill right. I know that fill down is Ctrl d but is there a way to fill across to the right? Thanks in advance
0
votes
1answer
13 views

Libreoffice : Getting cell value from a range referring to another range

i have a row '1' of minutes ( 1,2,3,4,5 ..) and below a row '2' of values ( 10,50,5,60,9..). How to get the minute of the greater value in cell C2 ?
1
vote
2answers
79 views

Excel/Calc INDIRECT+ADDRESS+ROW+COLUMN cleaner alternative

I'm not a regular Excel/Calc user, but I'm currently using LibreOffice Calc to record some money ins and outs. My two functions below works fine as intended, but still I'd like to know of a cleaner ...
0
votes
2answers
38 views

Calc, formula to find the index of the minimum value

In LibreOffice Calc, is it possible to write a formula which, given a column of unsorted data (see example below), returns the largest index of the minimum value greater than zero? The following ...
0
votes
2answers
27 views

How to find if 2 columns (or rows) in LibreOffice Calc are identical?

I have two long columns of data in LibreOffice Calc, something like below: Jimmy | Jimmy Bunk | Bunk Lester | Lester Prez | Prez ... These columns are most likely identical, that is, they ...
2
votes
2answers
41 views

Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?

I've located a web API resource that I'd like to access from LibreOffice Calc. It returns a simple JSON/JavaScript object that's basically just a handful of name value pairs, one of which I want to ...
3
votes
1answer
43 views

How does one use LibreOffice FILTERXML function on XML documents with namespaces?

I'm struggling to get the value of an XML node using the FilterXML() function when the XML document's document element has a namespace specified. Example: <?xml version="1.0" ...
1
vote
1answer
33 views

Generating unique number in LibreOffice Calc

So I have a document that allows a user to select a site/department from a dropdown list. Each site/Department has a unique extension prefix e.g. 10, 11, 12, 13, etc. When a user selects the desired ...
0
votes
2answers
33 views

How do I change date origin in libreoffice writer?

I am copying a chart from a Calc file to a Writer file. The Calc file has a date origin of 1904. When I copy the chart to the writer file, all the dates get shifted because the origin is (I suppose) ...
1
vote
1answer
56 views

Import from csv to Libreoffice Calc using 'two blank spaces' as delimiter

I want to import some data from a csv/text file to Calc. In the first column I'd like to have the 'Dummy text # i', and in the second column the numerical value. In the text file, 'Dummy text # i' ...
3
votes
1answer
68 views

Libreoffice, how to copy from Writer and pasting unformatted in Calc, by default?

I have the revision of a translated text, made by the client, I am the third party reviewer and have to fill up an .xls module with all the source texts, the translated one, and the amended by the ...
1
vote
1answer
18 views

stacked area chart with secondary data range

What I have is this: and I am trying to achieve this: Using Calc 5, how do I manage the second column to be recognized as secondary data range?
0
votes
1answer
103 views

Using libreOffice Calc function in macro

I am trying to create a basic macro function in calc to perform a hexadecimal to binary conversion with more then 10 bits. But I've got a lot of troubles. First, where can I find actual ...
2
votes
1answer
39 views

How can I access the spelling check correction in LibreOffice Calc?

LibreOffice Calc 5.0.4.2 on Ubuntu Linux 15.10 underlines misspelled words but does not display spelling check suggestions. Is there a way to add spelling check suggestions to the right mouse click ...
1
vote
0answers
20 views

How can I supress zero values in a LibreOffice line line chart? [duplicate]

I'm making a line chart counting users of various releases of a piece of software over the years. This is a very spiky line chart, where as new versions come into existence and then retire, lines ...
1
vote
1answer
60 views

Manually “drawing” line graph chart and export data

I would like to manually create a line graph and then sample the created line to generate a data series from it. Basically I have an idea, what a plotted values should look approximately and would ...
1
vote
1answer
29 views

MDETERM gives Err:502 on non-contiguous range of cells

I have the following setup: A 3x3 matrix in the cells A1:C3 and a 3x1 vector in D1:D3. I would like to calculate the determinant of a new matrix formed by the 1st column of the original matrix, the ...
0
votes
1answer
37 views

Get the value of one cell based on the value of another

Edited with additional context: Embedded in a LibreOffice-5 Calc spreadsheet I have a two column array where one column contains dates and the other column values. I wish to select the row based on ...
0
votes
2answers
62 views

Conditional Formatting based when equal/not equal to another cell in the same row

I have a spreadsheet like this in LibreOffice A | B | C | D -------+----------+---------+--------- 1 | A | 1 | B 2 | C | 2 | C 3 | D ...
4
votes
2answers
245 views

How does the group feature in LibreOffice Calc work?

LibreOffice Calc has as Group feature. The official help site just tells you where to find the button and what hotkey to use, but doesn't explain what grouping actually does: ...
3
votes
2answers
95 views

Perform Sum over Conditional Products

I do have a list like this ╔═════╦═══╗ ║ 1.5 ║ A ║ ║ 2.5 ║ B ║ ║ 2.5 ║ ║ ║ 5.2 ║ A ║ ║ 6.4 ║ C ║ ╚═════╩═══╝ and want as result: ╔═══╦═════╗ ║ A ║ 6.7 ║ ║ B ║ 2.5 ║ ║ C ║ 6.4 ║ ╚═══╩═════╝ So ...
0
votes
1answer
40 views

Is it possible to automatically separate line/scatter plot data into different series based on an additional column?

My data looks something like this: H, 2015-12-11T19:44:34, 3270 W, 2015-12-11T19:44:34, 2150 H, 2015-12-11T19:49:35, 3370 W, 2015-12-11T19:49:35, 2290 ... As a one-off, I can get ...
1
vote
1answer
56 views

Connect data points around missing data with different style of line on graph

I have an libreoffice-calc spreadsheet with some results of daily measurements. Some days are missed for some reasons. I want to create a graph showing this data, and I want to highlight all missed ...
0
votes
1answer
46 views

Using INDIRECT in cell validity

I have a spreadsheet contain 3 sheets. I want to set validity of column E of the first sheet based on text in the column D. The relation between these two columns are placed in the second sheet as ...
3
votes
0answers
46 views

Disabling spell checker in OpenOffice Calc (or LibreOfffice Calc)

Is there a way to disable the spell checker in OpenOffice Calc (or LibreOffice Calc) without disabling it for the other programs in the office suite? I would prefer to just disable it for a range of ...
1
vote
0answers
32 views

Libre Office - merge sheets

In one file I have two sheets, which share some of the columns. I'd need to merge the sheets based on column name, if the column does not exist, add it. Ie. I have Sheet 1 ...
1
vote
0answers
37 views

Change color for all set borders

Having a table with some complex borders. How can I change the border color for all borders in the table? Know I can change one by one, but that is a hassle. For example: +-----+--+ |1A 2A|3A| |1B ...
0
votes
2answers
146 views

LibreOffice Calc IF Function returns True when should return cell

I have been successfully using the IF function up until the last few days. I used this statement to return one of 2 values depending on whether or not an item is shaped: ...
1
vote
0answers
127 views

OpenOffice / LibreOffice ugly font rendering

I'm using Calc to generate some graphs of data I have collected. But somehow the font looks increadibly ugly in all of LibreOffice. I'm using Noto Sans CJK JP Light in this chart but pretty much every ...
1
vote
0answers
82 views

LibreOffice Calc changes letter from uppercase to lowercase under certain circumstances

I want to enter SXer into a field, however the X gets automatically converted to x. If I only write SX, the X stays the way it is. If I write SXEr, all letters stay as they are. But somehow I can't ...
1
vote
0answers
29 views

Libreoffice Calc adds extra commas or semicolons to formulas and doesn't return the answer

I am using LibreOffice 3.5.4.2 Build ID: 350m1(Build:2) On a Debian Jessie machine. Whenever I input any inbuilt calc/excel function, it adds random commas (sometimes semicolumns) between ...
0
votes
0answers
20 views

How to create a new Libreoffice-Calc document from template with specific name and save it immediately via commandline?

I'm looking for a way to create a new spreadsheet from a template, name it and save it immediately with the help of the command-line. I tried the -n switch, but it creates a temporary file called ...
0
votes
0answers
52 views

LibreOffice (Calc) | Search & Replace | unable to look for 'comments'

CTRL+F enables the SearchBar (quick search) CTRL+H brings up Find & Replace dialog (more options) I'm unable to find a way to look for comments that are within with a cell (with ability to ...
1
vote
1answer
21 views

LIBREOFFICE calc file can't be open by different computer

I have a .ods file on my USB drive and want to edit it at home and at work. But all documents I create are "stuck" to the computer I created it on. I have tried to save the file with password for ...