Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

0
votes
1answer
23 views

how to convert spreadsheet cells to units of time?

This is the goal: +------+---------------------+---------+---------------------+ | A | B | C | D | +------+---------------------+---------+-------...
0
votes
2answers
39 views

Is there a spreadsheet version of .rtf?

For a text document, there's multiple format options: .doc, .docx, .odt, .rtf, etc. I use .rtf for all text documents, because its compatible with nearly everything. My question is, is there a ...
0
votes
2answers
53 views

Excel / Libre Office Calc: Taking the average of a multiplication

I have three rows in Libre Office Weight: 0,2 0,2 0,3 0,3 Max. Points: 2 3 4 5 Points: 2 --- 4 4 0,74 For each column that Points is divided by Max. Points and ...
3
votes
1answer
15 views

How to set default currency type in Libre office

I am using LibreOffice 5.1.4.2 10m0(Build:2) on my Arch Linux. I would Like to set my default currency type to INR (English). That is, Now I do this : Right click --> Format Cells --> Currency --...
3
votes
1answer
17 views

Elapsed Time can't get hr:min with LibreOffice…?

How can I get my Elapsed Time column to come out in and Hour and Minutes format? I have the time cells formatted to Time & 13:37 and I've tried every available choice but no matter what for 24 ...
0
votes
2answers
19 views

Libreoffice Calc Saving TSV as CSV without asking

Create a tab-separated file saved as test.txt. Open in text editor and save: 1 2 3 4 This is a test (Those are tabs) Open it with Libreoffice Calc Click "Save" Libreoffice asks "Are ...
2
votes
1answer
28 views

Line chart in LibreOffice - data in rows (type, x, y) rather than in a 2-input table

How do I create a line chart in LibreOffice Calc if the data is organised in 3-tuples rather than in a two-input table? Example: Banana Jan 10 Banana Feb 20 Banana Mar 30 Banana ...
1
vote
1answer
41 views

How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?

Using Excel/Calc I have the following situation: a range of cells containing values; assume it's A1:B10, and a string I want to look for; for example, assume it is contained in C1. Now what I want ...
0
votes
1answer
29 views

How do I link to an external named range without DDE in LibreOffice Calc

I wish to bring over a total from one workbook to another without using DDE as that is deprecated and is causing me problems. The total is in a single cell with a range name. Both workbooks are in the ...
1
vote
0answers
22 views

Why VLOOKUP () doesn't work in this example in LibreOffice Calc?

This is a simple working example: A column has these names: اسم عربي واحد in A2 اسم اجنبي اثنان in A3 اسم ثالث ثلاثة in A4 اسم رابع in A5 The second column B2 has these numbers in front of the ...
0
votes
1answer
18 views

need a list of names to fill a column 2420 down each one each one to be multiplied 55 times and retain list order

I have a list of 44 names i need them to fill down a column in multiples of 55 each one to be multiplied 55 times I'm using libre office Does that make sense if it doesn't please let me know.
2
votes
1answer
19 views

Apply LibreOffice sum to multiple renamed sheets

I have a spreadsheet with many sheets, each tracking a particular topic. Within each sheet are columns of raw data, and a cell (always in F1) that contains a calculated stat for the raw data. I will ...
-1
votes
0answers
22 views

Merging/consolidating rows from different sheets

I have a spreadsheet with multiple tabs. Each tab/sheet contains a row of data relating to a pupil and I would like to combine all the different tabs together. That is, I would like to combine all ...
1
vote
1answer
27 views

using <li> on each newline

I have following pattern in every single cell in the coloumn in Libre Calc - Spec One: This is one Spec Two: This is spec two Spec Three: This is spec three Is there any way to convert this to - &...
0
votes
1answer
23 views

Merge/concatenate/append spreadsheet documents, not spreadsheet contents

There is much information here and on the web about merging/combining the data of several spreadsheets into a single one. I want to do something much simpler but cannot find how. I have several files,...
0
votes
1answer
15 views

Auto fill cyclically an array with a list of elements, setting only the first element

I have an array of 31 elements in Libreoffice Calc which I have to fill automatically with the days of the week (Mon, Tue, Wed, Thur, Fri, Sat, Sun, Mon, etc...) setting only the first element of the ...
1
vote
1answer
17 views

Disable autocomplete in LibreOffice Calc

Autocomplete can be nice, but for me it's usually not what I want in LibreOffice Calc. Is there a way to disable it?
-3
votes
1answer
25 views

Automatically Putting The Download Link Based on The Name

I have a worksheet which contains list of name. I need to put the link to the file which belongs to each name. Let's say the name is ALPHA CHARLIE then the file name which belongs to him is ...
1
vote
1answer
30 views

LibreOffice Calc: how to pad number to fixed decimals when used within formula?

In a LibreOffice spreadsheet, I want to display numbers (actually, prices) with two decimals. When a cell contains just a number, I can format it to do automatic padding. But sometimes I have cases ...
3
votes
2answers
97 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
25 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
110 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
11 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
1k 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
29 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
33 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
13 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
10 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
16 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
22 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: "user_id";"device_id"...
2
votes
2answers
51 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
122 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 ExcelSetup(...
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
14 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
155 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
42 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
56 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
65 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
67 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" encoding="UTF-8"?&...
1
vote
1answer
39 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
43 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
78 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
96 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
23 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
155 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 documentation?...
2
votes
1answer
49 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 ...