Questions tagged [openoffice-calc]
OpenOffice's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively
350
questions
2
votes
1
answer
2k
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 ...
4
votes
3
answers
3k
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 ...
3
votes
1
answer
1k
views
Prevent OpenOffice Calc from converting pasted numbers with decimals into dates
Whenever I paste numbers that have dotted decimals in them (let's say, 5.05) into an OpenOffice Calc Spreadsheet, it insists on converting them into dates (the above becomes 15-05-05).
I usually set ...
1
vote
1
answer
1k
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 ...
2
votes
1
answer
1k
views
Duplicate Chart in OpenOffice Calc without losing link to the sheet's data
I have created and formatted a chart and need the same one again to be filled with other data. The problem: In OpenOffice Calc 4.1.1, a chart's data is unlinked from the original data/sheet, as soon ...
1
vote
1
answer
973
views
OpenOffice Calc "if" formula for currencies
I have a spreadsheet that has many different currencies in it ($, euro, shekel). I want to display the actual price in the original currency and then show the converted price (in this case to shekels)...
1
vote
1
answer
117
views
Spreadsheet formula to display most recent prices
I have created a LibreOffice Calc spreadsheet with columns A-C. In column A, I have a list of parts purchased (in chronologically ascending order). In column B, I have a list of corresponding prices....
0
votes
1
answer
47
views
interrogating Bank data - in OpenOffice 4.1.1
I would be very grateful for any assistance with the following...
I have a worksheet of raw bank transaction data...I want to interrogate the data to display payments to the same source over the ...
0
votes
1
answer
34
views
Edit some Google Files on the desktop without installing Google Drive
I would like to edit a Google Tabel on my Desktop with out installing the Drive Client (I really only need this file) and with out using the browser. What can I do? Is there a plug-in for excle or ...
6
votes
3
answers
202
views
Alternate If() function in Excel/OOCalc
Countless times over years, I've wanted to use an If statement in Excel/OpenOffice Calc in the form of:
If(A (comparison operator) B, A, C)
or, equivalently useful:
If(A (comparison operator) B, C, ...
0
votes
1
answer
2k
views
Why the excel file of xml sized large ,is it there any way to reduce it?
i am trying to open 9 MB excel file in open office calc,it takes lot time ,so i opened it with archive manager to find whats wrong with the excel file
While opening it with archive manager i could ...
0
votes
2
answers
2k
views
Entering notes into LibreOffice Calc and OpenOffice Calc
In LibreOffice Calc and OpenOffice Calc, there is often a need to quickly enter several rows of text that will not be part of any calculation area. This is often done to write notes relevant to the ...
3
votes
2
answers
258
views
Which delimiter allows major spreadsheet programs to open CSV files by double-clicking?
I'm writing an iOS 8 app that allows you to export data to be sent as an email attachment. Currently, I'm exporting the data to a CSV file with 7 columns and x number of rows. When I first created it, ...
16
votes
2
answers
4k
views
In LibreOffice Calc is there a hotkey to edit the current cell?
I am wanting to edit a column of cells (for example, copy/pasted values have $0.005/hr and I want them to just be 0.005)
I can navigate with arrow keys, I can click to edit the existing value, but ...
0
votes
1
answer
983
views
Dynamic array/matrix generating within formula
The following formula:
=TRIM(A1:A20)
would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
0
votes
1
answer
134
views
How do I look up data from another table?
In this fictional example, I have two tables; one with employee information and pay rate:
The second with dates and the corresponding number of hours the employee has worked on that day (note that ...
1
vote
0
answers
286
views
LibreOffice Calc equivalent to Excel "Custom Views" feature
Custom Views are an Excel feature that comes very handy when, for example, you want to save a filtered version of a worksheet and then pass to another vision of the same worksheet with different set ...
4
votes
1
answer
8k
views
LibreOffice Calc equivalent to Excel "Evaluate Formula" feature
Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below).
Is there an equivalent to that Excel feature in ...
1
vote
2
answers
3k
views
How to give duration in excel or libre calc?
I have duration data like 5:23, etc... in m:ss format. I already typed the data, but I cannot create a diagram, because none of the mentioned excels understand it. If I change the data type to user ...
2
votes
2
answers
921
views
A Conditional Format to change the background color but keep the Date format
I have and OpenOffice Calc document that has a "table" with Text, Numeric and Date columns. I have a conditional format making every odd row have a different background color so it will be easier to ...
1
vote
1
answer
443
views
Count number of cells that match certain criteria with formulae
Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this COUNTIF(A1:A20,"...
1
vote
2
answers
1k
views
openoffice calc: sum of a function of elements of a row
If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1).
My question is about the possibility to calculate something like this
=sum(a1^2/(1+...
1
vote
1
answer
3k
views
Macro that will run in MS Excel and LibreOffice Calc
I have a spreadsheet that users will be working with in both Excel and LibreOffice Calc. I'd like to design macros that will work in both. I was thinking of code that would say: If Excel Then [VBA ...
0
votes
1
answer
2k
views
How to add double quotes around data in tables using Calc
I am trying to load my data into a CRM, but am having a lot of trouble. The data simply wont load at the same time, but all the files work fine individually. Support advised that I make sure I'm using ...
3
votes
1
answer
83
views
Can you apply names in OpenOffice Calc the same way you can in Excel?
In Excel, you can apply names to a formula (semi)automatically by selecting the formula/formulae in question and then using the Apply Names function.
This will change something like =B3+C9*D16 into =...
1
vote
1
answer
172
views
Comment visibility in open/libre office spreadsheets
I'm getting moderately perturbed by how difficult it is to see whether there's a comment in a cell in LibreOffice. As it stands a tiny box appears in the upper right-hand corner in the cell. Does ...
1
vote
0
answers
1k
views
Libreoffice-calc: Apply a macro to a csv-file on the command-line
With Libreoffice/Openoffice it is rather straightforward to do these steps:
Import csv file
Copy macro from another file
Run macro
Remove macro
Save file in csv and xls formats (no macros, just data)
...
1
vote
1
answer
482
views
Pasting Hex Colors Into OpenOffice
I'm attempting to paste a very large table of data containing names of colors and their HTML (hex) equivalent into OpenOffice, but it continues to format my colors as weird data types.
For example, "...
0
votes
1
answer
677
views
different colors in cells as value increases
The conditional formatting that I am using right now is .. if cell equals 1 then this color .. if cell equals 2 then this color and so on.
Is there a way I can format it for more than just the 3 ...
14
votes
1
answer
5k
views
How to highlight whole row and column under cursor in OpenOffice Calc?
I need to work with large table. Something like in this illustation image will be helpfull for better orientation:
Is there some settings which makes this possible?
2
votes
1
answer
360
views
How to Assign Background Color Using "if" Condition [duplicate]
How can I assign the Background color of a particular Cell in an Open Office? Likewise greater the Somevalues should having Green, and less than value should having Red background?
3
votes
3
answers
10k
views
How to copy/paste values in OpenOffice
I have inserted the formula =today() which gives me today's date.
I want it to be retained tomorrow as today's date.
In Excel I would use Copy / Paste Values to overwrite the formula with the ...
6
votes
4
answers
8k
views
Get DayName from Date in OpenOffice Calc?
I am having difficulty getting the day name from OpenOffice Calc. I am able to get the day Number representation from a date with the following
WEEKDAY("2008-06-14"; 1)
which returns
7
As we ...
1
vote
2
answers
2k
views
Calculate the SUM() time from columns with mixed types
Consider the following table:
+------------------+------+--------+
| Total worked | Total| Tasks |
+------------------+------+--------+
| 07:30| 09:20|Day 10 |
+-----+-----+------+--...
0
votes
1
answer
41
views
OpenOffice Calc - 11 Pages in spreadsheet, but print interface only shows 2
I am working on a spreadsheet using OpenOffice and need the pages to be printable.
You can print the first two, but thats it. If in the print interface I select all sheets it only shows 1 and 2 with ...
0
votes
2
answers
449
views
Using ISBLANK formula to write a variance
I've got a formula or a plan £ in C4, and the actual £ to be entered in D4 cell. E4 contains the formula D4-C4.
I would like E4 to be blank until both C4 and D4 have values.
0
votes
1
answer
2k
views
How to plot IP address frequency distribution chart on Excel or Calc?
I have one column of data:
IP Address
1.2.1.2
1.2.1.2
1.3.4.1
1.3.4.1
1.3.4.1
1.3.4.1
1.3.4.1
1.10.0.5
1.10.0.5
1.10.0.5
2.1.2.1
2.1.2.1
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
...
0
votes
1
answer
272
views
link data from another .ods file
I'm using openoffice calc and I have 2 .ods files (complete.ods and summary.ods).
I have 1 cell from summary.ods which depends on complete.ods. I managed to get the value by using this formula:
=DDE(...
15
votes
9
answers
29k
views
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
I have a LibreOffice Calc spreadsheet which pops up a message asking me whether I wish to update external links. I did not add external links myself, but did copy data from another Calc window at one ...
-1
votes
1
answer
3k
views
How to remove Open Office spreadsheet duplicates in a column while keeping rows together?
I have a spreadsheet with a list of column data of contacts, first name, last name, address, phone number, email...
I want to remove the duplicate email addresses but the entire row may not have ...
0
votes
1
answer
467
views
LibreOffice Calc Error 518
I'm trying to achieve the following in LibreOffice (did it with Excel already, but importing this excel-created file produces the same error).
I have 3 columns
(A) (B) (C)
ECTS Mark Done-...
1
vote
0
answers
48
views
How to pull all rows containing the same date into a new sheet in OpenOffice Calc
I have 15 spreadsheets (one for each client); each has a column for the date and then additional information in the other columns such as 'action' and 'time spent'. This allows me to see all the work ...
2
votes
1
answer
73
views
Clear cell changed metadata in OOO Calc
I am trying to figure out how to clear the red highlighting around cells in Calc (OpenOffice 4).
Here is an example:
It is not affecting my work but I would like to clear them as they are ...
0
votes
1
answer
391
views
Open Office Calc, Round up or down
I'm trying to output retail prices rounded to either a 4 or an 8 from various formulas
Is is possible to round one figure to the nearest of each of these?
1
vote
1
answer
97
views
calc: Extract string from range of cells
I´m trying to figure out a easy to understand way to extract a string (that is in a range of cells) from a range of cells...
That is the range of cells I have to look for a specific string (in a ...
1
vote
1
answer
5k
views
OpenOffice Calc - calculating percentage for every cell in a column
I have a somewhat common task of calculating percentage of column. For example I have a column with 20 numbers on each row. I add 21st row with formula for the sum of all those 20 rows and then I need ...
0
votes
1
answer
855
views
Libreoffice calc export to pdf. Some characters appear different
I'm having the following problem: when I try to export a .ods file to .pdf on Libreoffice Calc 4.3.2.2, I use some Unicode characters. Most of the time they appear in the pdf just fine, but in some ...
2
votes
1
answer
21k
views
How to change the date format (DD/MON/YY) in OpenOffice spreadsheets?
I want to change the date format from default format to another format (with months)in OpenOffice spreedsheets.
0
votes
2
answers
2k
views
How to change the default font in LibreOffice 4
How do I change the default font in LibreOffice / OpenOffice. So that when I open a new spreadsheet document it doesn't revert to use the standard default font (Liberation Sans/Serif)
How to set the ...
3
votes
1
answer
995
views
Min of two dates in OpenOffice Calc
I have two cells in OpenOffice Calc (A1 and B1). In cell A1, I have "2014/01/01" and in cell B1, I have "2014/06/03". I want to find the minimum/smallest of the values in A1 and B1. I actually have ...