Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
0
votes
0answers
17 views
Can I limit a range of cells to a specific set of values?
I'm creating a table in LibreOffice calc. One of my column is actually a choice among several fixed categories - let's say it's a choice of one of several possible strings.
Can I constrain the set of ...
1
vote
1answer
12 views
LO Calc won't strip quotation marks from text
Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ...
1
vote
1answer
26 views
Libreoffice Calc, macro set Text Alignment Indent, or property documentation?
In Libreoffice Calc it is possible to use macro's to set the text properties of each cell, to format the layout and font. I was able to find some property names online, in code examples and forums.
...
0
votes
1answer
42 views
Sort spreedsheet according to groups and dates. Also leave blanks when no group size doesn't match
I have a spreadsheet that looks like:
There are two sets of columns, let left one and right one are supposed to be ideally be the same (row by row), but they aren't and I need to filter them such ...
0
votes
0answers
57 views
Drag-to-duplicate row, preserving data underneath, in Calc
I have a spreadsheet in LibreOffice Calc that I use for invoices. In this is a row that lists task and cost. When I select the cells in this row, I can drag the lower-right corner and duplicate the ...
-3
votes
2answers
84 views
Excel: how to count and group rows
I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert).
I need to count each name of Column A and count each name of Column B. And then I need ...
0
votes
0answers
18 views
How to create readonly table from raw data
If I have a large table of data, essentially like a database, can I create another sheet or range that is a filtered subset of the full dataset? If so, will it stay up to date when the main table ...
1
vote
0answers
20 views
Size of dropdown / selection list in Libre Office Calc
With the data validation tool I made a list of categories that are shown in a selection list for easier data entry. This list is very small and I cannot just enlarge it with the mouse (see screenshot)....
1
vote
0answers
52 views
How to copy multi-line text from LibreOffice Calc WITH quotes
I've been searching and found this question... How to copy multi-line text from Excel without quotes? ...which is almost exactly the opposite problem I have.
I am using LibreOffice Calc and I have ...
0
votes
1answer
23 views
Long Calc Sheet - subtotal at bottom of page, transfer to next page?
Assume i have a LibreOffice Calc sheet spanning multiple pages in printout. Is there a way to calculate a subtotal for each page and print it at the bottom of the respective page as well as at the top ...
1
vote
3answers
44 views
How do I format a number in an “=CELL” type of statement?
I have a spreadsheet where cell A1 is 1234.56789. In cell B1, I have ="$"&A1. This shows up in B1 as $1234.56789. How do I make it show up as $1,234.57 in B1?
3
votes
1answer
66 views
How do identify the source of a #NAME? error in Libreoffice Calc?
My LibreOffice Calc spreadsheet contains the following formula:
=IF(D8=0,'',C8*D8/B8)
If B8, C8, and D8 all have values, then this works. But if they are empty it doesn't work, I get a #NAME? error ...
1
vote
0answers
34 views
Can I use Excel keybindings in Libreoffice Calc?
LibreOffice Calc has its own set of keybindings, which is different to those in Excel.
Is there a "compatibility mode", or some way to match Excel's keybindings?
I know I can change the bindings one ...
2
votes
2answers
153 views
LibreOffice Calc: conditional formatting based of column / cell value
Unfortunately I cannot grasp how the conditional formatting works. I have a sheet like that:
I have defined 2 cell styles "Low" and "High" and I want cells in columns A-E to have one of these styles ...
0
votes
1answer
62 views
LibreOffice Calc - Multiple INDEX-MATCH and copy to other cells
I'm just starting to use LibreOffice Calc to try to manage my finances, and have imported CSVs of statements from my bank.
The first task I would like to accomplish is to display, in an easy-to-read ...
3
votes
2answers
114 views
Align data columns in LibreOffice Calc according to numbers in another column
I have three columns:
Column A contains a count from 1 to 100, representing different sample stations. The numbers are in sequence.
Column B is independent of column A. It contains selected ...
0
votes
1answer
43 views
Copy/Paste a formula in Libreoffice without adjusting the cells
I have a formula in Libreoffice Calc which I would like to move (Cut and Paste) to a different cell. I would like the formula to exactly stay the same without the cells in it being adjusted by the ...
0
votes
1answer
87 views
Disable auto formatting in LibreOffice Calc
Whenever I enter data in LibreOffice, may it be a date or a number, LO is formatting it as such instead of leaving it as text. That terribly annoys me because I want the text I enter to remain in that ...
1
vote
2answers
302 views
Insert the current date and time (DATETIME-function) with a single shortcut into a cell in LibreOffice Calc
I know there is the CTRL + ; shortcut to insert the current date and CTRL + SHIFT + ; to insert the current time.
However I have two issues with that:
1) I would like to have one shurtcut for both
...
1
vote
1answer
31 views
CSV file - Combine title if value=YES in another cell - Libreoffice / Excel / Googledocs
I have a file with text divided into cells that I want to group into another cell.
Cells are:
A, B, C, and D are what I have. In each coluimn, if the value is Yes, I would like the Result column to ...
0
votes
1answer
8 views
LiberOffice Calc apply style with only specifc attributes
Consider this simple time tracking spreadsheet:
I figured out how to use conditional format to apply a selected style to a region, in this case I use "Accent" for Fridays and Saturdays, and this ...
0
votes
1answer
26 views
Two related cells
In A1 I have price, for example 1000. In A2 fill percentage, for example 10%. Then in B2, calculate A1*A2 = value. I would like when i fill percentage in A2, in B2 calculate value and vice versa - If ...
0
votes
1answer
242 views
LibreOffice Calc: What is the data range syntax?
I want to specify a graph's data ranges, but I don't know what format the data range specifier strings are in. Specifically, I'd like to specify all cells in Column A starting and including row 2 for ...
0
votes
0answers
8 views
How can I get a report on Autofilter stats in LibreOffice Calc?
When I do Data -> Autofilter, can I have a report of how many different values there are and how many rows are in each for a column?
3
votes
1answer
39 views
In LibreOffice Calc, can be a digit added directly after a regex match in single find/replace operation?
I want to add character 5 after certain pattern found in the column (e.g. 4 digits).
Example: aa1234bb → aa12345bb
Method:
Find: ([0-9]{4}) with Regular Expressions option checked
Replace: $15 ...
0
votes
1answer
65 views
Find and Replace in LibreOffice Calc
So I have a Price column and all the values has a £ prefixed. These are formatted string. I tried via the Find & Replace function to replace all of these with nothing, but I can't seem to get it ...
2
votes
0answers
59 views
When I try to edit Y-axis limit on LibreOffice, the data in the chart disappears
Has anyone encountered such an issue? Am I missing something?
When I choose axis limits manually from Right Click > Format Axis > Scale, the data in the chart disappears or gets hidden. I am ...
2
votes
1answer
20 views
Format cells to show fractions always with 30 on denominator on LibreOffice Calc
I use LibreOffice Calc to calculate proportional values in fraction format with denominator 30.
30 represents a commercial day in our country, that means the number of days in a month for ...
2
votes
1answer
166 views
How to set line height in Libre Office Calc
I have entered two lines in a wide enough cell. I used CTRL+Return combination at the end of the first line to wrap the text and entered the second line.
I was able to set different style for the two ...
10
votes
2answers
697 views
Convert the multi-step sum of booleans into a single formula
I have a few sensor readings and I'm checking to see if the values are in the acceptable range.
To do this I use an IF to check if the values are > or < the calculated (average) value. The ...
2
votes
1answer
56 views
References in color-scale conditional formatting formulae for ranges
I'm using Libreoffice Calc (v6.1b).
Suppose I have numeric data in sheet column, A and I'm applying conditional formatting to sheet column B. I want each cell of B to have a color on a scale ...
0
votes
0answers
44 views
How to make custom currency in LibreOffice Calc?
I'm trying to create a custom currency (ETH) format for some value tracking in LibreOffice Calc. I followed this instructions on this link, but the numbers (e.g. 0.05) I enter in the formatted cells ...
1
vote
1answer
216 views
LibreOffice Calc #name error
Tell me, please, what's the problem. I'm trying to write an IF, but I'm constantly getting a #name error. Moreover, even with the simplest conditions, like 0 <1. The syntax was looked up in the ...
0
votes
1answer
145 views
LibreOffice does not recalculate sums
I have a Calc sheet in LibreOffice (6.0.3.2 on Ubuntu 18.04).
In one field I have the formula =sum(D2:D85).
Then I changed some values in the range D2 to D85, but the sum was not recalculated. If I ...
0
votes
1answer
87 views
How to use a cell value as reference in a formula
I have been trying to get a cell reference as text to be used as reference to another sheet:
Got a dropdown menu cell (A2) returning a text string (e.g.: Sheet2, Sheet3) Now I wanted to reference ...
1
vote
1answer
242 views
How can I add duplicate rows to LibreOffice Calc to make there be one per day?
I have some data like this (it's stock market data)
29/07/2015 35972.82 32056.2 17751.39
30/07/2015 35961.86 32046.43 17745.98
31/07/2015 35848.13 31945.08 17689.86
03/08/2015 ...
0
votes
0answers
149 views
How to export single sheet to PDF in LibreOffice Calc from CLI?
v5.3.7
scalc --convert-to pdf:calc_pdf_Export *.ods converts all sheets to PDF, but I must only export one named sheet. (All of the files have the same named sheet.)
Thus: is there an option to ...
0
votes
1answer
432 views
Format seconds.milliseconds into minutes:seconds.milliseconds
How could I go about having a cell where the display shows a time format, but is edited in raw seconds and milliseconds?
I want to have a cell where when clicked on and a value typed, the user types ...
0
votes
0answers
74 views
LibreOffice Calc copy&paste not working for text
I have a column denoting "tasks" and it spans about 10 000 rows. It looks like this (other columns are omitted)
task
1
1
1
1
2
2
2
2
3
3
3
4
4
4
5
5
5
Now I need to make these numbers into text ...
1
vote
1answer
138 views
How to do a regex substitution on a string in an LO Basic “macro”?
This seems like something that should be extremely easy to do but it's not - it's certainly not obvious how to do it, anyway.
I want to write a function in LibreOffice Basic that takes a string, a ...
1
vote
2answers
109 views
VLOOKUP returns #N/A when criterion is a reference
LibreOffice Calc 5.1.6.2
I want to use VLOOKUP function with criterion being a reference to a cell again containing a reference. But it doesn't work, it returns #N/A. Interestingly, when I replace ...
1
vote
0answers
61 views
Show contiguous time periods without data in Libre Calc pivot table, pivot chart?
A minimal example: our table has sales in 2015, 2017, but not 2016.
Date Sales
01/02/15 $500
10/11/17 $1000
You make a Pivot Table:
Data > Pivot Table > Create
Row Fields: Date
Data ...
1
vote
1answer
232 views
LibreOffice Calc color-coding cells categorically
I have a spreadsheet of text data; each column has a limited allowable set of string values. I have a second sheet in the file which, for each column in the first sheet, contains each allowed value ...
0
votes
1answer
189 views
Lag with LibreOffice Calc when scrolling quickly through rows/columns
I have moved to LibreOffice from Microsoft Office, basically because I refuse to pay the Price for Microsoft Office.
I like LibreOffice as it offers everything I need and does pretty much the same ...
1
vote
1answer
51 views
How to find the last filled-in cell in a row?
I have the following data sheet. It has a row of dates, where every date has an A and a B value. I would like to find the last filled-in B value for that range.
In the following screenshot, I would ...
1
vote
2answers
139 views
How do I preserve column grouping in Libreoffice Calc?
I am using Libreoffice 5.2.0.4
In Calc (the spreadsheet), I am trying to build an appointment book. Since this is designed a bit like a calendar, I have rows and columns that I want to be able to ...
1
vote
1answer
551 views
Why does LibreOffice Calc change CSV file Character Set?
I have a .csv file export (actually printed to csv file) made on a Windows 10 machine. Working in Libreoffice Calc on Archlinux, Why can Calc open a file using settings Character Set: Western Europe (...
2
votes
1answer
189 views
Change background color on a cell if that cell doesn't have a number entered
Trying to change the background color of a cell if there isn't a number entered in the cell and the date in an adjacent cell is older than today. in libreoffice calc. I have tried conditional format ...
1
vote
1answer
1k views
Use a Python function as formula in LibreOffice Calc cells
Since LibreOffice Calc supports macros, and the builtin functions are somewhat limited, I'd like to write my own functions in Python and then use them as formulas in individual cells. Ideally, I would ...
0
votes
0answers
56 views
libreoffice calc crashes when I want to add Dialogs
I'm using libreoffice calc 5.3 in Fedora 26. I'm trying to add some dialogs to my sheet. But when I want to edit dialogs my libreoffice crashes and it shows me a dialog with 2 options: "force quit" ...
