OpenOffice.org's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively

learn more… | top users | synonyms

0
votes
2answers
12 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 relevant notes. When ...
3
votes
2answers
59 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, ...
0
votes
0answers
10 views

What formula in OpenOffice calc can automate game show points as needed? Details in text

I host a trivia game show in which the answer to every question is a number. The eleven teams/players closest to the correct answer without going over receive points - 15, 12, 10, 8, 7, 6, 5, 4, 3, 2, ...
1
vote
1answer
13 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
0answers
18 views

How to open a password protected ODS file via command line / external script?

I created a password protected libreoffice's "calc" file (ods). Now I'd like to open it, or convert it to plaintext format, either via command line, or with an external script, e.g. python. What I ...
0
votes
0answers
17 views

Text is being duplicated in OpenOffice - How can I stop it?

I generally do not use OpenOffice. However, my Microsoft Office decided to deactivate itself recently, and will not reactivate due to the product key being used too many times (which it hasn't). I am ...
0
votes
1answer
52 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
1answer
15 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 ...
0
votes
0answers
24 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 ...
1
vote
1answer
90 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 ...
0
votes
2answers
43 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
2answers
35 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 ...
0
votes
0answers
27 views

Copying a sheet in Open Office Calc without moving sheet references

Good day! I am fairly new to using Calc, so do forgive me if I ask something obvious, but I am having a tiny issue with copying sheets. To start with, I want to clarify that I do know that if you ...
1
vote
1answer
21 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 ...
1
vote
2answers
65 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 ...
1
vote
1answer
118 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
1answer
22 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 ...
2
votes
1answer
16 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
1answer
22 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 ...
0
votes
0answers
21 views

Apache OpenOffice 4.1.1 OSX 10.8.5

After losing an extensive spreadsheet last night, I started all over today. Got it completed. Had saved (as .xls) manually using save/save all in the dropdown menu periodically throughout. Closed. ...
0
votes
0answers
57 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 ...
1
vote
1answer
40 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
0answers
31 views

Concatenate a reversed range in Libre Office

I'm kind of new with programming, but I have to make an excel document, that I can use interactively because the output of one of my programs doesn't fit with the input of the others... I have lot of ...
0
votes
1answer
17 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 ...
1
vote
1answer
77 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?
1
vote
1answer
56 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?
0
votes
1answer
72 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 ...
0
votes
0answers
39 views

Rearrange columns from fiscal year to calendar year in OpenOffice spreadsheet

I have twelve monthly columns plus a total column in a spreadsheet that was originally created for an April through March fiscal year. Now I need to rearrange the columns for a calendar year, without ...
1
vote
3answers
201 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
2answers
78 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
1answer
20 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
2answers
71 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
1answer
132 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
0answers
13 views

Use formula to copy cell data/formula to another cell

I'm trying to use Libre Calc's formula functions to copy data and formulas from cell A1 to B1 given certain data in A2. I've searched Calc's help/docs but don't see anything. The closest is the VB ...
0
votes
1answer
36 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: ...
1
vote
1answer
474 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
1answer
398 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
1answer
101 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 ...
1
vote
0answers
30 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
1answer
27 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
1answer
47 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?
0
votes
0answers
60 views

Open Office: Simple Chart with dates/numbers?

I'm using OpenOffice Calculator. I have some data like this: Date | Messages Jun 2 2011 | 345 Jun 4 2011 | 212 Jun 5 2011 | 221 I'd like a very simple graph with the dates on the x-axis and ...
1
vote
1answer
34 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 ...
0
votes
1answer
648 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
1answer
152 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 ...
-1
votes
1answer
393 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
2answers
504 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 ...
0
votes
1answer
47 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 ...
0
votes
1answer
34 views

How to display OpenOffice error details

So after few years I had to use open office once again. It's just as fun as it always used to be. I tried very simple expression LEFT(C1, 1) which is supposed to pick first character from cell C1. ...
0
votes
1answer
66 views

OO Calc: Detecting Blank Cells and Replacing with Cell above?

So annoyed I can't find a simple answer to this simple question! I'm using OpenOffice Calc. I want a formula which will detect if the cell is blank, and if it is to replace it with the contents in ...