Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
546
questions
1
vote
0answers
17 views
how to change address based on a value in another cell in Libreoffice Calc?
I need to pull data from the source spreadsheet $Units_Weekly, from a changing column.
The start is column O ----> $Units_Weekly.$O2
Column name changes in A50. The next column name will be P,
it'...
0
votes
2answers
22 views
How to count number of cells greater or less than 0 (zero) between two dates in Excel?
I have spent a couple of hours trying to find a way to achieve this, but I'm stuck. Please help!
What is the formula to count number of cells greater or less than 0 (zero) between two dates in Excel?
...
0
votes
1answer
31 views
Convert csv-like file (with custom separators) to ods, odf, xls, or xlsx
I have a csv-like file with complicated data and would like to open it in LibreOffice Calc, Microsoft Excel, or another spreadsheet program.
What I mean by csv-like:
Fields are separated by the ...
0
votes
0answers
14 views
LibreOffice Calc arbitrarily crops first and last two data points
I'm currently using LibreOffice 6.1.5.2 for Linux. When creating a chart from a simple XY dataset, the program just arbitrarily chops away the first and last few datapoints. I would like to restore ...
0
votes
0answers
10 views
How to pivot multiple rows into one by placing them next to each other in LibreOffice Calc?
Let's say I have data like this, showing what video games each person owns (not my actual data but that's sensitive information):
Name Game Publisher
---- ---- ----...
0
votes
0answers
99 views
How to sync two columns and update the following cells in the rows?
Say I have two files, which have first columns are identical:
Note, that only the first columns are synced. The second columns are different.
Is there a way to make a rule between two files that,
...
0
votes
0answers
15 views
Libreoffice Calc paste over filtered rows
I have a problem pasing text over filtered rows in Libreoffice calc.
Imagine I have 1000 rows and, when I apply a filter over them, the result is 50 visible rows.
Well, if I copy a text with 50 ...
0
votes
0answers
12 views
Shade part of chart in Libre Calc
I have recently switched over to using open source software. For a school assignment I am suppose to create a bell curve and shade part of it. I have already create a graph showing the bell curve, but ...
0
votes
1answer
45 views
Conditional Format of cell based on the format of another cell
Referring to screen shot, I want to programmtically format a cell like so:
If cell B4 and cell F4 are both red text, then format cell J4 to be red text.
(and similarly for all other cells).
I see ...
1
vote
1answer
40 views
LibreOffice Calc: how to convert minutes into decimal hours
I have the value 0:15:00 h:mm:ss - I'd like to get the value 0,25 hours - how can this be done? I tried simply dividing 0:15:00 by 60 but that didn't return what's expected.
0
votes
0answers
47 views
LibreOffice Calc, how to start multiple instances with the same file open?
Say I have a spreadsheet file /home/amoe/foo.ods. Imagine that I already have this file open in Calc. I want some way, perhaps a command line option, that would enable me to run localc /home/amoe/...
0
votes
0answers
43 views
LibreOffice Calc - Get the value of a cell of the next empty row in calc
I have a four-column table with multiple rows with headings Weeks Amount Balance and True.
How can I get the Amount of the row that the last column that doesn't equal true?
1
vote
0answers
26 views
LibreOffice Calc: modify default cell colour
How can I change the default cell background colour in LibreOffice Calc? This to avoid changing it manually every time I create a new document.
2
votes
1answer
122 views
Libreoffice Calc: Pinning a column to multiple sheets?
I have a libreoffice calc document with multiple sheets. In the first sheet, I have a column that I want to be repeated as the first column in all other sheets.
I know that I can reference cells ...
0
votes
0answers
82 views
How can I use Python macros in Libreoffice calc?
Is it possible use Python macros in Libreoffice calc 6.0.7.3 and Ubuntu 18.04? I meant something like when Calc runs a macro, it would save the data to CSV format, open it in Python, modify the data ...
1
vote
0answers
19 views
How to apply SUBTOTAL count distinct in Libre Office Calc?
I've applied =SUBTOTAL(3, RANGE:RANGE) but I need to count only the unique values.
is there any way like this =SUBTOTAL('function-distinct', RANGE:RANGE)
How to apply SUBTOTAL count distinct in ...
0
votes
3answers
41 views
Vlookup is not working fails with N/A return despite i have value
My Vlookup function isn't working, it fails with N/A despite the fact that I have values. Have a look in screenshots how can i solve it
See File
0
votes
0answers
9 views
Exporting documents includes original filename
If I want to export an Excel sheet to PDF in LibreOffice, it always includes the original filename at the bottom of the PDF pages. Is there an option to disable this behaviour?
0
votes
1answer
38 views
Conditionally accumulate rows
I have tabulatory data as follows
2020-01-01 | yes | 5
2020-01-01 | no | 3
2020-01-02 | no | 2
2020-01-03 | yes | 7
2020-01-10 | no | 4
2020-01-11 | yes | 9
2020-01-12 | yes | 22
and I want to ...
0
votes
1answer
15 views
What Libreoffice function or method will copy the formatted text of a cell rather than the data
A1 = "=now()"
Which when formatted looks like:
January
I want to copy that "January" into a string of text such as:
="The month is" & A1
How do I copy the text? The output I get now looks like:
...
0
votes
1answer
61 views
How to display a scrolling roll of names?
I have to organize a roll call vote with over 100 people.
The next 4 or 5 names must be displayed on a screen, so that voters see their name and prepare to speak up. Ideally, this is a scrolling list ...
0
votes
0answers
21 views
In charts in LibreOffice Calc, remove the annoying grey box bottom left
I am creating pivot charts in LibreOffice Calc, based on single column data (with Count values). I have pretty much access to all elements in the pie charts I need to format as desired, however there ...
0
votes
1answer
37 views
How to autogenerate a date in a libre calc
I think this is easy but I am not finding solutions online. I have recurring bills and would simply like date values to change every month in their respective cells.
For example if cell A2 due date ...
0
votes
0answers
8 views
Hide rows with future date
I have todo-list in calc with a priority column containing dates and the words "important" and "not important".
I would like to only show the rows with the word "important' and dates in the past. I ...
0
votes
1answer
11 views
Librecalc EXACT String Match Always Produces #NAME? Error
When attempting to do an EXACT string match in Librecalc I always get a #NAME? error:
Moreover, Librecalc decapitalized the 'Buy' that I entered to 'buy'. This is suspicious behavior that may be ...
0
votes
0answers
29 views
How Can I Break Up a Spreadsheet into X rows?
I'm using LibreOffice Calc on Linux Mint 19 and am trying to find an easy way of turning a 100k row spreadsheet into 10 10k spreadsheets.
I tried seeing if an option was available in the Data menu ...
0
votes
0answers
32 views
Import/Export Named Ranges and Expressions using text-only format
I am looking for a method to export/import "Named Ranges and Expressions" from/to LibreOffice Calc using exclusively text files. I am well aware of several methods which involve moving or copying a ...
0
votes
1answer
103 views
Sorting a list of electronic components?
In a spreadsheet the bill of materials of electronic components lists the components by their values, for example, 1R0 (for 1 Ohm), 1k0 (for 1 kilo-Ohm), or 22p (for 22 pico-Farad), 1n0 (for 1 nano-...
0
votes
0answers
11 views
Can I globally toggle all forms of autocorrect in Calc?
Sometimes I'm editing tables of plaintext data (e.g. .csv) and need Libre to not touch it; don't mess with capitalization, don't turn 00001 into 1, don't correct spelling, etc. There are settings for ...
1
vote
1answer
85 views
What is the formula for calculating time in and time out?
I am creating a spread sheet where I have just time in B2 and time out B3. I have it in the 09:00:00AM format. I want to get the total of hours worked in cell C, but I am not having any luck with ...
0
votes
0answers
32 views
Group data stored in column according to choices stored in another columns
I have the following set of data:
A B C D
1 | Student name | Space | Information | Sea |
| ------------ | ----- | ----------- | --- |
2 | John Foo | ...
0
votes
0answers
25 views
Get addresses for all duplicates in a spreadsheet
I am trying to analyze output of performance benchmark. My spreadseet consists of multiple Linux top outputs spawned every 0,5 s for about a minute. I need to identify keywords that will help me ...
0
votes
0answers
32 views
LibreOffice Calc copy/paste to Google Spreadsheets skips empty rows
The problem is in that, that when I copy selection range from LibreOffice Calc to Google Spreadsheet, the result are copied with empty rows missing. The row in the selection must have all cells empty ...
1
vote
0answers
8 views
Libreoffice: create multiple rows from 1, depending on row content
I have a style sheet in Libreoffice, like this:
DateA | DateB | DateC | DateD
---------------------------------------
John | 1 | 1 | 0 | 1
Mary | 0 | 1 | 0 | 1
...
0
votes
0answers
35 views
Automatically update time-dependent conditional formatting in Libreoffice Calc?
I have a conditional formatting that is meant to highly the line representing the current day, but it also has to apply different formattings depending on the specific row. One of the conditions reads:...
2
votes
0answers
64 views
CLI command to print sheet2 from libreoffice calc .ods
Is there a commandline option to print sheet2 from file.ods using the terminal?
I tried loffice, localc, lp, lpr and am unable to specify the precise sheet I want to print. Any ideas on this?
0
votes
1answer
8 views
Extend Libreoffice calc Formula Without Overwriting Background Color
How to extend formula without overwriting background color in LibreOffice Calc?
This is similar question to [Extend Excel Formula Without Overwriting Background Color
][1] but for LibreOffice.
1
vote
2answers
42 views
How can I sort out a list of numbers according to certain characters in the middle of these numbers?
So, at my job, we want to divide the work of checking certain items according to their registered numbers. Here's an example of these numbers:
0022499-80.2017
0200781-98.2016
0021322-13.2016
Items ...
0
votes
0answers
18 views
Libre Calc: how to create a special cell so that I could reference to it from other sheets?
I am trying to create a monthly budget sheet. It consists of 2 sheets. The first sheet (Sheet1) consists of 2 columns - column EXPENSE_TYPE and column AMOUNT. Its purpose is to display a total amount ...
0
votes
1answer
14 views
Find value in selected column
I want to find a value in a particular column.
In Excel, I would select the column and press Ctrl + f. The same procedure in Libreoffice-Calc searches the entire spreadsheet. There doesn't seem to ...
0
votes
0answers
27 views
How can I make cell references change to reflect naming?
In a LibreOffice calc spreadsheet, I have a bunch of cells with formulas referring to other cells, without special (nick)names, e.g. D5 or H3, Now I set some name for my cells, e.g. D5 gets the name ...
0
votes
0answers
36 views
How do I set Calc to copy to clipboard in csv format?
Looks like when I select cells and press ctrl+c, Calc exports it in a tab-separated format to the clipboard.
How can I make it export in csv format, instead?
0
votes
1answer
33 views
Displaying values in a spreadsheet up to X days away from current date
I'm looking to get some assistance in untangling the mess that is a data entry and summary spreadsheet I've had for the last few years in LibreOffice Calc. The general layout of the spreadsheet's ...
0
votes
0answers
75 views
Fixed column width for CSV files
I use LibreOffice (6.1.6.3) Calc to work with CSV files.
Some of these files have very long text in their "cells". When I open such file in Calc, the Calc tries to make the column as wide as possible....
2
votes
1answer
163 views
How do I format time to MM:SS in Libre Office Calc?
Libre Office Calc 6.0.7.3.
I'd like to enter the time 5:39 in minutes and seconds in a cell. I highlight the cell and select Format > Cells from the menu. In the 'Format Cells' dialog, I enter MM:...
0
votes
0answers
64 views
LibreOffice Calc - read-only files
We have a number of raspberry pi devices that access files on our network storage device. The pi's have only read access to the various files: jpg, pdf, doc, xls, etc.
When the clients open an xls ...
0
votes
0answers
51 views
Change the default font used in diagrams in LibreOffice 6.1
I'd like to change the default font used in diagrams in LibreOffice. I created a new document, changed the font to Arial and saved it as a template. However, when I open a new document, this only ...
0
votes
0answers
239 views
After editing CSV file, size increases
i'm trying to edit an CSV file. The main CSV file is 2.466KB after i downloaded it.
After i increased all Prices in one Collumn, and saved it as an .CSV file, the file size increased to 51,252MB.
I ...
0
votes
0answers
28 views
How do I use conditional formatting in LibreOffice to check if there are any duplicates of values between different sheets?
I am trying to compile a spreadsheet of all the macros, keyboard shortcuts, and keystroke permutations each of the programs on my computer uses, so that I can have them readily accessible for ...
0
votes
1answer
153 views
LibreOffice Calc »regex« function – which regex format is applicable?
I want to find a string like a DOI in a sentence and extract the result in a cell. I found the »regex« function in LibreOffice Calc (current Ver. 6.2.4.2) which tells me that it would exactly do what ...
