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
1answer
32 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
18 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
31 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
22 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
32 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
31 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
0answers
15 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
21 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
68 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
38 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
16 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
76 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 ...
1
vote
1answer
41 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 ...
0
votes
1answer
16 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
16 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
20 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
22 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
59 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, ...
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
169 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
49 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
87 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
13 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
26 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
168 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
32 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
76 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
21 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
33 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
390 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
70 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
103 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
70 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
66 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
242 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
403 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
50 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
21 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
45 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
39 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
110 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
52 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
69 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
27 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 ...
3
votes
1answer
312 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
97 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
416 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
51 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
532 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
153 views

Calculate the SUM() time from columns with mixed types

Consider the following table: +------------------+------+--------+ | Total worked | Total| Tasks | +------------------+------+--------+ | 07:30| 09:20|Day 10 | ...