Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [openoffice-calc]

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

0
votes
0answers
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
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 ...
0
votes
2answers
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
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 ...
0
votes
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
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 ...
-1
votes
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
3answers
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
0answers
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
2answers
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
1answer
40 views

how to convert spreadsheet cells to units of time?

This is the goal: +------+---------------------+---------+---------------------+ | A | B | C | D | +------+---------------------+---------+-------...
1
vote
1answer
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 ...