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
14 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
28 views

how to convert spreadsheet cells to units of time?

This is the goal: +------+---------------------+---------+---------------------+ | A | B | C | D | +------+---------------------+---------+-------...
1
vote
1answer
12 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 ...
-1
votes
0answers
13 views

with which feature i could input automated entries in a column automatically? [closed]

If I want to input name of months in a column automatically in the active cell then which feature I should use to input automated entries in a column automatically?
0
votes
0answers
5 views

How to get rid of “sheet name” and “page number” on LibreOffice Calc Print? [duplicate]

When Ever I print my spreadsheet, I get sheet name on top of the sheet and page number at the bottom. I want to remove them. I could not find an option to disable them. Is there any method to achieve ...
0
votes
1answer
23 views

Merge/concatenate/append spreadsheet documents, not spreadsheet contents

There is much information here and on the web about merging/combining the data of several spreadsheets into a single one. I want to do something much simpler but cannot find how. I have several files,...
0
votes
1answer
27 views

Count the amount of times a value appears combined with a certain event

I basically have the following list: How can I count the amount of times John won? I have tried using both countif and countifs but have find no positive result. I either get "#VALUE!" or a simple ...
0
votes
0answers
17 views

How to find and replace the character “$” in Open Office Calc

How to find the character “$” in OpenOffice Calc and replace cell's value times 2 Practically looking for all the prices in a column and i want to double that. EX:$25 result $50 Can i use the find ...
0
votes
0answers
16 views

Libreoffice Calc - how to drag cell leaving formula in original cell?

Is there a way/setting to leave the formatting in a specific cell after using 'cut' or dragging the content of the cell away? 'Protecting' the cell is not what I'm looking for since that disables ...
1
vote
1answer
15 views

Fill right in open/libreoffice

I'm using Libreoffice calc for an application where it's necessary form me to fill right. I know that fill down is Ctrl d but is there a way to fill across to the right? Thanks in advance
0
votes
0answers
21 views

Open Office Calc; Can I paste the active cell value into the filter cell in a pivot table?

I use two pivot tables to provide date for a summary view of a stock's position in my portfolio. In Excel I was able to select any cell with the stock symbol and then using a macro, insert that ...
0
votes
1answer
14 views

Is it possible to have a sum of text REPLACE'd cells in openoffice.org calc?

I have a spreadsheet in calc where I paste dollar values ("$2.50") from another source, but sometimes these are in CAD and appear as "C $3.75", so I would like to be able to keep those strings as they ...
0
votes
1answer
45 views

Sum over cells across different files in Calc

I'd like to do a sum or sumprod over cells across different files. More precisely: I have a list of file paths to other .ods files which contain data organized in the exact same way. Say in cell A1 '...
1
vote
1answer
13 views

Add multiple rows to an OpenOffice Calc spreadsheet on mac

What is up with both Excel and Calc with adding multiple rows on Mac? In both cases the Insert|Rows simply adds one row - without any option to enter a count of rows to add. So what are we ...
0
votes
1answer
25 views

Can not set default template in openoffice for Mac

The following reference attempts to explain how to set a new default template for Calc. https://help.libreoffice.org/Writer/Changing_the_Default_Template I have followed the directions but there ...
3
votes
1answer
44 views

Replicate data from separate files in OpenOffice Calc

I have several .ods files full of data (say data1.ods, data2.ods etc). These files use exactly the same format, so that calculations based on data1.ods would be equally relevant on data from data2.ods....
0
votes
0answers
16 views

Openoffice doesn't show open window

I use Open Calc to open some CSV files directly from Windows Explorer but the open window doesn't show up over all the rest of opened windows and I must to minimize all the other windows one by one to ...
0
votes
1answer
67 views

Change color of row according to column value (by regex)

I'm quite new to excel so this could be dumb question. I need to change color for those rows which has suffix .com in their second column. I use OpenOffice but I can change it using Microsoft Excel ...
0
votes
1answer
37 views

Libreoffice calc how to filter by a list of ID

I have 2 spreadsheet: - one with 14000 raw that contains all the information, first column is the ID - one with the first column containing ID i'm interested in I need to get the first speedsheet ...
1
vote
1answer
65 views

Manually “drawing” line graph chart and export data

I would like to manually create a line graph and then sample the created line to generate a data series from it. Basically I have an idea, what a plotted values should look approximately and would ...
0
votes
1answer
65 views

Get the value of one cell based on the value of another

Edited with additional context: Embedded in a LibreOffice-5 Calc spreadsheet I have a two column array where one column contains dates and the other column values. I wish to select the row based on ...
1
vote
1answer
66 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 ...
3
votes
0answers
57 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 ...
2
votes
1answer
26 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 ...
0
votes
0answers
52 views

Sublime Text 3 doc encoded as cp1252 won't save as cp437

I have this vexing situation with a plain text document created in Sublime text 3 (OS X Mavericks 10.9.5) and saved in win1252 encoding. After I have pasted a selection from an OpenOffice Calc ...
1
vote
0answers
148 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 ...
1
vote
1answer
92 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 ...
0
votes
0answers
18 views

Openoffice Calc cell does not show typed numbers

Openoffice cell data when entered shows only the letters AAAAA. How to convert this into actual typed numbers or text?
1
vote
1answer
149 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
1answer
55 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
1answer
20 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
1answer
17 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 ...
0
votes
0answers
22 views

Force New Line in Date Format — Open Office Calc

I want to display a date and time in a cell with the date on one line and the time on another. In "Format Cells" I have this under "Format code": MMM. D, YYYY \@ H:MM AM/PM Is there way to do ...
0
votes
0answers
28 views

Reset OpenOffice.org preferences

To be specific, I want the "Do you really want to overwrite existing data" message to reappear. I receive this message when copy pasting data, however, NOT when dragging data. That's because I ...
6
votes
3answers
75 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
0answers
13 views

Conditional Formatting based on a formula value in Open Office

On my spreadsheet I would like to change the color of cells based on the amount divided by a set value and counted as days. For example I have a $9000 job in A1. I would like to do $1000 a day in ...
-1
votes
1answer
315 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
2answers
71 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
2answers
114 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
15 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
45 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
272 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
1answer
103 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
28 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
0answers
61 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
765 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
86 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
157 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
1answer
99 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
2answers
405 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+...