Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

0
votes
2answers
51 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
8 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
14 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
271 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
21 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
19 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
7 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
4 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 ...
2
votes
1answer
459 views

How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols. I believe the problem is to do with the two ...
0
votes
1answer
336 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 ...
1
vote
3answers
16 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
31 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 ...
2
votes
1answer
32 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
2answers
36 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 ...
0
votes
1answer
44 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 ...
6
votes
5answers
10k 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 ...
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
11 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
67 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
37 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
24 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 ...
3
votes
5answers
7k 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 ...
3
votes
1answer
41 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" ...
15
votes
3answers
13k views

LibreOffice Calc: How to get total for HH:MM:SS cells

I'd like to take a group of cells in the HH:MM:SS format, and add them up. I don't want to have the value wrap on the 24 hour clock. I just want an absolute total of the values. (The sum function ...
1
vote
1answer
31 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 ...
1
vote
1answer
57 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 ...
0
votes
2answers
30 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
53 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
58 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
17 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
96 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
36 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
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
32 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
57 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 ...
0
votes
1answer
1k views

Libreoffice Calc - Fill with Sum is not working

I want to add 2 columns of data and output the sum into a third column, with a =sum(a1+b1). With many rows of data, I try dragging down the highlighted cell to Fill the rest of the output column with ...
4
votes
2answers
209 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
94 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 ...
2
votes
1answer
4k 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 ...
0
votes
1answer
38 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 ...
2
votes
1answer
2k 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
1answer
50 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 ...
3
votes
1answer
8k 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 ...
0
votes
1answer
44 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
43 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
28 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 ...