# Questions tagged [openoffice-calc]

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

**0**

votes

**0**answers

16 views

### OpenOffice Calc: extract integer from string #12,246 in Office Products (See Top 100 in Office Products) [closed]

I'm trying to get to
12246
from
#12,246 in Office Products (See Top 100 in Office Products)
within
OpenOffice Calc

**0**

votes

**0**answers

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

**0**

votes

**2**answers

29 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

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

**0**

votes

**0**answers

191 views

### Linear interpolation lookup table in LibreOffice Calc

I have a table of data like this
0 -105.0
10 -114.3
20 -121.8
30 -127.0
40 -129.6
50 -130.8
60 -131.2
I've discovered the LOOKUP() function, which lets me input a value from the first column ...

**0**

votes

**0**answers

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

**0**

votes

**0**answers

15 views

### How do I enter time hh:mm without am and pm being automatically entered?

How do I stop automatic entry of am and pm when entering time.
I am trying to enter time as a calculation of time worked but not whether it is am or pm.

**0**

votes

**0**answers

25 views

### How can made MS-Excel Find and Replace dialog box as a bar which is docked below the window same as LibreOffice Calc interface option?

I have an opportunity in LibreOffice Calc which is very attractive for me.
That is an option for UI interface.
Made the Find and Replace dialog box to a sub-bar. And possible to dock it below the ...

**1**

vote

**1**answer

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

**3**

votes

**0**answers

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

**0**

votes

**0**answers

23 views

### Open Office Calc: make entire spreadsheet plain text, always

I only use Calc for data. I don't need or want fancy styles being applied for any reason, ever. Is there a way to do this?
I want the entire spreadsheet to be plain text, always, regardless of what I ...

**1**

vote

**1**answer

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

**-1**

votes

**1**answer

42 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

109 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

101 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

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

**0**

votes

**0**answers

44 views

### Can I create a sheet which sums groups of N rows in LibreOffice Calc?

I have a LibreOffice Calc spreadsheet which has this structure
APPLES ORANGES BANANAS
5 0 10
15 2 12
3 2 105
...

**3**

votes

**2**answers

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

**0**

votes

**2**answers

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

**0**

votes

**0**answers

88 views

### Libreoffice Calc creating every pivot table of two variables possible

I have a big table of data that looks like this :
image
and I want to compare them in a pivot table with two variables each time. I find it very long to select every variable. Is it possible to ...

**-1**

votes

**1**answer

47 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

154 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

398 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

100 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

96 views

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

I don't what happended in this open office installation. Its on college computer (Linux), so I can't do a reinstallation without admin access.
I have English language selected in Language setting, ...

**1**

vote

**2**answers

401 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

86 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

393 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

147 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

310 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

69 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

76 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

334 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

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

**4**

votes

**3**answers

4k 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

349 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

45 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

183 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

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

**0**

votes

**0**answers

25 views

### Is there a way to pre-define a character set (like utf-8) for a .txt file you're opening w/ ooo calc, instead of having to select it?

I have a php script that spits out .txt files, and I was wondering if I could add something to define the character set that ooo calc would read/see when opening so that ooo calc will know ...

**0**

votes

**0**answers

11 views

### Calc formula to determine Remembrance Sunday

I have a perpetual calendar and have just about figured out special dates... except for the first Sunday on or after (in this case) Nov 11th.
I could do something like:
DATE(calendarYear,6,1+7*2)-...

**0**

votes

**0**answers

22 views

### LibreOffice/OpenOffice Calc not letting to write phones

I input phones from keyboard as:
+9 (999) 999-99-99
After each cell edit the program shows a message that it detected an error in a formula:
LibreOffice Calc found an error in the formula entered....

**2**

votes

**1**answer

99 views

### LibreOffice Calc paste as if always into formula bar

I am forever frustrated with how cut and paste works in LibreOffice.
For example, I find that phone numbers are treated as numeric and leading zeros go missing, or plain text gets pasted with font ...

**0**

votes

**1**answer

76 views

### Frequency function in OpenOffice 4.1.2 Calc

I want to change the"data" field dynamically to 1 of 18 array names
example:
=frequency(LG_1;FOCUS)
where LG_1 is the name of 1 of the 18 arrays, and FOCUS is the name of the classes array which ...

**3**

votes

**3**answers

1k views

### How to create an array of values in LibreOffice Calc?

I'm using LibreOffice 5.1.4.2 Calc and need to calculate the internal rate of return for a certain payment (say, in cell A1), a certain number of times (say, 100).
If I had the value repeated 100 ...

**1**

vote

**0**answers

59 views

### Alternate row shading by ID - Conditional Formatting in OpenOffice

02902982085 4 a ? <-- yellow
02902982085 # 1 r <-- yellow
02902982085 b $ 0 <-- yellow
01395235224 w z [ <-- blue
01395235224 a - 5 <-- blue
...

**0**

votes

**2**answers

233 views

### Case-sensitive regular expression search formula in OpenOffice

In OpenOffice Calc:
FIND() is case-sensitive but does not support regular expressions.
SEARCH() is case-insensitive and supports regular expressions.
How (in a formula) can I do a case-sensitive ...

**0**

votes

**1**answer

40 views

### how to convert spreadsheet cells to units of time?

This is the goal:
+------+---------------------+---------+---------------------+
| A | B | C | D |
+------+---------------------+---------+-------...

**1**

vote

**1**answer

2k views

### Limit number of rows and columns in OpenOffice Calc

Is there a way to limit the number of rows and columns shown in a sheet in OpenOffice Calc?
I have resorted to manually hiding rows and columns outside the area that I want, but this is tedious, and ...