# Questions tagged [openoffice-calc]

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

325
questions

**2**

votes

**1**answer

220 views

### Both '.' and ',' for decimal separator in LibreOffice Calc?

I have read How to set decimal separator in Libre Office Calc? but I haven't found an option for this:
Is it possible to set both . and , as decimal separator in LibreOffice Calc?
In France it's , ...

**0**

votes

**1**answer

25 views

### Runtime error when iterating over cells in LibreOffice Calc

I have a table where the values are very large, in order of millions. Basically I want to iterate through all the cells(In a particular column) and divide each of them by some number (like 10,00,000).
...

**0**

votes

**0**answers

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 ...

**-3**

votes

**2**answers

52 views

### How to subtract (compute delta) between two sheets?

I have two snapshots of data in big tables on two different sheets. Basically the sheets have identical content but the numbers are from different moments of time. I need to compute the delta between ...

**1**

vote

**1**answer

67 views

### Is it possible to combine two IF(AND functions into one? (OpenOffice Calc 4.1.7)

Is it possible to combine two IF/AND functions into one? (OpenOffice Calc)
I have two IF/AND functions that work perfectly well, however I need them to both work on the same line. Is there anyway to ...

**0**

votes

**0**answers

14 views

### OpenOffice Calc - Highlighting the higest value in multiple columns

Question is similar to a the following question: OpenOffice Calc - Highlighting the higest value in a column but it's not quite what I'm looking for.
I have a table that looks like the following
I'...

**0**

votes

**0**answers

20 views

### Filtering long lists in OpenOffice Calc

I have 3 keyword lists separated into columns: Column A, Column B, and Column C
Column A is the primary list, containing 100s of keywords. Columns B and C are secondary lists, containing many, but ...

**1**

vote

**1**answer

59 views

### How to copy a new line from libreoffice calc to txt?

I have the following function in cell 'B2' a libreoffice calc document:
=CONCAT(A1,CHAR(10),A2,CHAR(10),"end")
Which, with in cell A1="function plotName = getPlotName(plotData)" and A2=" plotName ...

**0**

votes

**0**answers

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

**1**answer

92 views

### How can I stop Open Office Calc from changing the data?

I am using Apache Open Office Calc to view CSV data. I just noticed that Calc rounds the data on saving, reducing its precision, or at least I think that's what's going on.
I show below the same data ...

**0**

votes

**1**answer

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

**1**answer

107 views

### How to track frequency of multiple randbetween() outcomes in a single cell

I am attempting to simulate a large quantity of dice rolls using Excel or LibreOffice Calc. I managed to find here a formula to generate multiple random numbers based on values specifying how many ...

**1**

vote

**1**answer

123 views

### OpenOffice Calc import special characters in CSV incorrrectly?

I'm trying to import a CSV file to OpenOffice Calc, but the program is reading special characters incorrectly.
For example, one of the fields in the CSV is "Puma La Cerámica", but Calc imports it ...

**1**

vote

**1**answer

30 views

### Is there any formula for OpenCalc which can count rows that contain some values but do not contain other values?

I have the following cell structure in OpenCalc:
|--A--|--B--|--C--|--D--|--E--|
|--1--| A1 | B1 | C1 | D1 | E1 |
|--2--| A1 | B1 | C1 | D1 | E1 |
|--3--| A1 | C1 | D1 | E1 | F1 |...

**0**

votes

**2**answers

37 views

### Spreadsheet- How to update the value of the same cell

I want to update the value of a the cell based on the current value of that cell and value of another cell like A1=A1+A2what is the correct formula

**2**

votes

**1**answer

80 views

### OpenOffice Calc: Graph ON/OFF times

I have a simple spreadsheet with every row containing times (24h) something was switched on and off again. So it looks something like this:
10:20:15 10:25:36
11:09:02 11:15:54
13:00:38 13:01:...

**0**

votes

**1**answer

69 views

### Calc pie chart for 25000 rows with long tail

I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be ...

**1**

vote

**1**answer

22 views

### Is there a way to average data that has a common value in a column?

I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are ...

**0**

votes

**2**answers

93 views

### Calc print several sheets in same page

I have a Libreoffice Calc spreadsheet that holds around fifty sheets (each one with no more than a dozen valued rows); this is good for data organization — each sheet holds a sequence of events ...

**0**

votes

**1**answer

69 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 ...

**0**

votes

**0**answers

69 views

### Summing Euro amounts in Libreoffice calc

I have a spreadsheet with a column full of Euro amounts. This spreadsheet is created from a CSV and the amounts are interpreted as strings (text) rather than as numbers.
I've been trying to get the ...

**1**

vote

**1**answer

30 views

### Open Office Calc: How to find a set of values (like any digit) in a string?

Modeled off a solution I found for Excel, I tried the following in Open Office Calc and got an error:
=FIND({1,2}, A1)
The goal is to find the coordinates in the string at A1 where either the number ...

**4**

votes

**0**answers

150 views

### In LibreOffice Calc, how can I change the cell selection black ring to something lighter for dark backgrounds?

In LibreOffice Calc, how can I change the cell selection black ring to something lighter so it can be easier to see for dark backgrounds? I will attach an image to show what I mean. It would be nice ...

**1**

vote

**1**answer

1k 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 ...

**-1**

votes

**1**answer

64 views

### Is it possible to “countif” cell contain value of specific order?

I work on a warehouse using WM apps that resulting exported data for daily transaction. We use open office calc for spreadsheet data processing. The data contain cells for location, with format like:
...

**1**

vote

**1**answer

442 views

### How do I lock some columns and some rows in a Libreoffice Calc worksheet?

I am using Libreoffice Calc 5.2.0.4.
I am building an appointment book. This book has many sheets/ pages.
I am not experienced, so I was experimenting on different sheets. And now I have three ...

**0**

votes

**0**answers

22 views

### Office Calc, matching Column header to cell [duplicate]

Snippit from current spreadsheet
I'm having trouble with a formula for 'G' Column, I want to have the header the cell with a (negative or positive) value to be copied into the 'G' (or Code) cell that ...

**1**

vote

**1**answer

268 views

### If cell contains value then 'Column header'

Looking for help
A B C D E F
1 Food Petrol Rent Wages
2 **Food** 12
3 **Petrol** 35
4 **Rent** 120
5 ...

**2**

votes

**0**answers

244 views

### Dropdown Lists in both Excel and Open Office

I have an Excel workbook which works as a time sheet. Users select the project they worked on and how many hours they spent on it that week. The project can be selected from a dropdown list showing ...

**4**

votes

**2**answers

2k views

### Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)

I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it.
This number must be copy-pasted to a numeric cell in Excel or Calc. The file ...

**1**

vote

**2**answers

1k views

### Adding a percentage to every cell in a column in LibreOffice calc

I have a spreadsheet with about seventeen hundred rows and I need to add 3% every cell in a column.
Example:
Column
------
1800
1500
5435
3583
3544
etc
I need to figure out what 3% of 1800 is then ...

**-1**

votes

**1**answer

57 views

### Matching cell to a range and inputing matched cells corresponding cell

I used "=IF(MATCH(A1;E$1:E$4;0);A1)" in Openoffice (FROM THIS AWESOME SITE) to search a range to match a cell. Is there a way to report the corresponding cell value of the matched range cell? E.g.
...

**0**

votes

**1**answer

306 views

### Sync sheets in Libreoffice

Hopefully someone can help.
I have an eccommerce dropship website on Woocommerce which I update via CSV using WPAllImport. I currently download a CSV Spreadsheet from my supplier which contains ...

**1**

vote

**2**answers

605 views

### How to just add conditional formatting to a cell without overwriting the cell's direct formatting

I have different cells with different background colors. Depending on a Conditional Formatting I'd like to add borders around some of these cells.
Applying the borders works but the cells' ...

**0**

votes

**1**answer

140 views

### Match 2 values on excel / calc

I have this table structured as described below with some data for example:
+-------+-------+-------+-------+-------+
| name1 | code2 | data3 | name4 | code5 |
+-------+-------+-------+-------+------...

**2**

votes

**1**answer

139 views

### Open Office is not showing in English, even though the English language is selected

I don't understand what happened with this Open Office installation. It's on a college computer (Linux), so I can't do a reinstallation without admin access.
I have English language selected in ...

**2**

votes

**2**answers

3k views

### Enter the same data into multiple, non-adjacent cells in OpenOffice.Org Calc

I want to enter the same data into multiple cells that are not adjacent.
I have selected the cells and pressed CTRL + ENTER but this doesn't work.

**0**

votes

**0**answers

102 views

### How to change scale of units in OpenOffice

I have made a chart in OpenOffice but in x-axis the values are very small ~10^-3. How can I just change the numbers so that they are scaled without changing the data?
Here's a picture of the x-axis:
...

**0**

votes

**1**answer

1k views

### Open Office Calc lookup vlookup

I am trying to return a value to a cell, columns C to H depending on what is chosen in the drop down in column B.
The figures I need to return are held on a separate sheet .
Any help with this would ...

**-2**

votes

**1**answer

258 views

### Convert minutes and seconds over 60 seconds to whole minutes and seconds

I have times consisting of minutes and seconds displayed with a decimal as a separator, and the seconds can exceed 60 (two digits, so up to 99). For example, 3.92 equals 3 minutes and 92 seconds.
I ...

**2**

votes

**1**answer

601 views

### OpenOffice Calc macro in Python - macro triggered by a click of a button, how to get a name of that button?

I am really new to this and OpenOffice Developer's Guide is too difficult for me to understand or I'd help myself.
I have a large number of rows with data and in each row I want a button that would ...

**1**

vote

**0**answers

93 views

### Does LibreOffice Calc calculation time out or have any limits?

I have a relatively large spreadsheet (2300 rows, out to column EL) and lately the auto calculate has been unreliable. I would have to click on a cell I was sure was incorrect, hit F9 and see its ...

**0**

votes

**1**answer

141 views

### OpenOffice calc colorize data points differently

I gathered some FTP file size statistics for my boss and I wondered if OpenOffice calc had the ability to colorize the data points differently. I want to color green the data points within one ...

**0**

votes

**2**answers

1k views

### How do you get multiple if statements working within the same open office calc cell

I am trying to sort a column out in open office and need the cell values to stay between 10 and 0. I currently have an If statement as follows: If(X6>0;(((S7/V7)-($B$6))/$B$4)+$X$5;0)
I need to ...

**0**

votes

**1**answer

84 views

### Open Office Calc - What formula can be used to find a value in a column based on another value being reached in a different column

So I have 2 separate columns (Column A / B for this matter) which use different formulas to increment for approximately different amounts. I need a formula / equation which when column B reaches a ...

**3**

votes

**3**answers

6k views

### Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

Besides using conditional formatting, is there any way in LibreOffice Calc (or OpenOffice Calc) to highlight every other row in order to make the spreadsheet easier to read?
LibreOffice has a serious ...

**2**

votes

**1**answer

471 views

### Preventing capitalization in OpenOffice Calc (or LibreOffice Calc)

In OpenOffice Calc (and LibreOffice Calc), the first letter of each cell is auto-corrected to be capitalized.
Is it possible to prevent this, without affecting the behavior of the other applications ...

**0**

votes

**1**answer

57 views

### How to apply a fomula multiple times at different intervals to entire column? OO Calc

I am using Open Office Calc on windows 8.1 Pro
I have the following data already sorted nicely.
A B C
--+-----------------------
1 | Bmw Gold £80
2 | Bmw Silver £60
3 | ...

**0**

votes

**1**answer

274 views

### Unexpected Behaviour using VLOOKUP in OpenOffice Calc

I am using VLOOKUP in OpenOffice Calc on Windows and trying to use a lookup table to populate some other cells.
My Lookup table ranging A1:B6 is
Car 4 Wheels
Florida No
Bus 8 ...

**0**

votes

**1**answer

177 views

### Multiple EqualTo Strings in Conditional Formatting IF statement

I am using Open Office Calc on Windows
When using Format > Conditional Formatting and selecting Formula is drop down under Condition 1, I am typing my formula
IF($B10="BMW")
This works great.
...