OpenOffice.org's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively
1
vote
0answers
20 views
How to make just one text column be unquoted in final CSV?
I'm using OO calc and i have one column with phone numbers that have leading 0s, which are vital for the number to be valid. I can format the cell as text but i do not want the numbers to be quoted ...
0
votes
0answers
15 views
OPEN OFFICE CALC - How do I use conditional formatting give me a background based on the distance from a date AND value of a cell?
If I have "Alpha", "Bravo" or "Charlie" in column "A" and "Annual", "Semi-Annual" or a blank cell in column "B", How can I use conditional formatting to change the background color of a cell in the ...
-2
votes
0answers
27 views
Pop-up over/under a cells' formula when editing it via the Formula Bar
For instance, using the formula =IF(F9=15;"ttttttt";IF(F9=30;"ssssss";99)) , a rectangular window will pop up ( highlighted in a pale yellow background ) below the cell containing the formula with ...
1
vote
0answers
10 views
LibreOffice - import CSV: have semicolon checked by default
When I import CSV data or copy it into a spreadsheet, the text import window shows up. There you can specify what separator character is used. By default, TAB is checked. All my imports use a ...
0
votes
1answer
14 views
Why is Openoffice Calc automatically rounding the sum of two sums?
I have two columns which have been summed. When I sum the two cells with those results, openoffice rounds the sum every time. If I only enter two numbers and then sum them, it operates normally. ...
1
vote
0answers
19 views
Autocomplete only on demand in OpenOffice Calc
In Open-/Libreoffice Calc, it is possible to enable the auto-completion of cells. That means the cell is filled e.g. with a string that starts with the characters already typed. This may give unwanted ...
0
votes
1answer
60 views
How can I use extremely large numbers in my spreadsheet programs?
If I try to calculate large exponents in LibreOffice Calc or OpenOffice.org Calc, the results come out to be erroneous. See these two examples where I calculate 13^18,
Using LibreOffice Calc
Using ...
0
votes
1answer
21 views
Pasting data in openoffice Calc
I want to paste data in Openoffice Calc but it doesn't seem to work.
The example with the first row (of 124 rows), this is the data in a .dat-file:
0.592 0.349 1.333 0.617
When I want ...
1
vote
0answers
25 views
OpenOffice Calc - How to substitute multiple characters?
I need to substitute non-English characters in French words to the closest English charachers. For example:
"Combien ça coûte" Becomes "Combien ca coute"
I also need to remove some punctuation. Is ...
1
vote
1answer
54 views
How to color rows based on cell value in Open/Libre Office
How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't ...
0
votes
0answers
18 views
Change border color in OpenOffice - macro
When I have table in Open Office and I´ve got there some values (for example 23 and 26) and I need circumscribe all numbers beteen this values, how can I do it in macro?
For your imagination, I post ...
0
votes
2answers
49 views
Open/LibreOffice Calc data “extend selection” stops at blank rows
I have a spreadsheet and have right clicked on a column header then selected Sort from menu. The dialog box provides an option to extend the selection, which I accept. The problem is that the ...
1
vote
1answer
47 views
Find the Nth instance of a text string within a range?
I have a list of names next to a list of shifts. I want to collate a list of people on each shift by day, which will automatically and dynamically update. I can't work out the best way to do it.
In ...
1
vote
1answer
47 views
Database workflow advice? [closed]
I've got a product database that I need to update a couple of times a week and am looking for some advice on what workflow I should setup. I can export my database from my online store as a CSV, I ...
1
vote
0answers
25 views
LibreOffice Calc - Search and replace merged cells
Is there any way to find merged cells with the Find & Replace dialog and replace the cell content in all of them?
1
vote
1answer
31 views
LibreOffice Calc - apply style to neighbouring cells
I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data.
Here is ...
1
vote
0answers
22 views
How to disable command+scroll zooming in openoffice calc
Is there anyway to have command+scroll in OSX do nothing instead of scrolling? I press the keyboard shortcut all the time and it throws my focus off. I have looked extensively in forums, but often the ...
0
votes
0answers
22 views
OpenOffice Calc; Random selection of cells that have not yet been randomly selected?
In effect, I'm looking to create a table with each cell representing a card in a deck, and I would like to simulate drawing the cards randomly while avoiding drawing the same card (or cell) a second ...
0
votes
0answers
22 views
How to apply AutoFilter on multiple sheets in OpenOffice / OpenOffice Calc?
I have a calc document with two sheets. When I apply the AutoFilter on sheet 1, then apply AutoFilter on sheet 2, the first AutoFilter on sheet 1 is gone again.
It seems that OpenOffice Calc can only ...
0
votes
0answers
58 views
Getting date of last change as a cell value in Open Office Calc
I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key ...
2
votes
2answers
72 views
OpenOffice Calc regex returns wrong value - what's wrong with my regex?
I have a column of text with values concatenated by my bank, as in a typical monthly statement.
Here is an example (column A240):
OCT 26 BALANCEPROTECTOR PREMIUM $10.05
I'm trying to set the ...
0
votes
0answers
108 views
OpenOffice Calc - incorrect converting to date format
I have problem with OpenOffice Calc...I need to work with many values of date/time in text format "D.M.YY H:MM", for ex. today 13.3.13 12:30 ... But if I try to format this strings like Date, it ...
0
votes
3answers
375 views
How to change the hh:mm:ss format in spreadsheets
Currently, I have a data set which is "mm:ss" and needs to stay / be calculated that way.
The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" and to add ...
0
votes
0answers
20 views
LibreOffice adjust CSV opening options
How can I get LibreOffice to NOT to adjust the cell height when opening a csv file?
0
votes
0answers
31 views
Can I use a function in an OpenOffice Calc standard filter?
I would like to do something like:
Field name Condition Value
Column A > NOW()
to get rows refering to events in ...
0
votes
0answers
65 views
Apply Header Image to OpenOffice Spreadsheet
I am attempting to apply a background image to my Excel(Open Office Calc) spreadsheets in Open Office Calc.
My Problem: The header image is cut off by the spreadsheet columns. How can I make the ...
1
vote
0answers
156 views
OpenOffice Calc: Cell Background/Border Color
I am trying to create a spreadsheet with colored cell backgrounds. But I encountered this strange bug, where the background of a cell is bigger than the shown border, so coloring a cell will actually ...
0
votes
1answer
69 views
How to format a date in Calc?
When I enter 09/02/2013 00:00:00 in a cell in Windows 7, the cell displays 02.09.2013 00:00.
I have specified the proper Format code in Format Cells:
In Windows XP, the outcome is as expected. The ...
0
votes
0answers
106 views
excel conditional formatting change background of row [closed]
I got 900 rows. From A to Z.
I want to change bakcground color based on Z value on each row.
Formula looks like this for one row
$Sheet1.$Z2="!!!!"
Now just formula will work for Z2 cell and will ...
0
votes
1answer
31 views
Looking for suggestions on how to arrange 7900+ lines of text in Calc
I have a dataset with the complete text of the Greek Novum Testamentum (New Testament); see an image here.
Each line is one verse. I will find the book and chapter boundaries with the help of these ...
0
votes
1answer
31 views
How is the LOOKUP function named in German? [closed]
The english version of OpenOffice Calc does have the function LOOKUP.
How is this function named in German (Deutsch)?
1
vote
1answer
423 views
Libreoffice calc relative and absolute cell reference on copy-paste
I'd like to create a simple Calc sheet, where column A contains the days, B contains how many hours of work i had done and C the price for that day. The price is simply a multiplication of hours (B ...
1
vote
1answer
128 views
Binary spreadsheets with bitwise operators [closed]
I am looking for a way to easily construct binary spreadsheets using bitwise operators such as SHIFT, AND, OR and XOR.
I've looked at Gnumeric but can't find any function that'd allow you to set the ...
0
votes
0answers
39 views
OpenOffice Calc Searching a table containing drawn lotto numbers
I have a table with drawn lotto numbers (1-49), prizes etc.
Date n1 n2 n3 n4 n5 n6 city prize Retailer
...
0
votes
2answers
36 views
OpenOffice.org Lowercases second capital letter
How can I turn of the feature in OpenOffice.org Calc which converts CCleaner to Ccleaner?
0
votes
1answer
60 views
Split sheet by rule (filter) in Open Office
I want to split a large sheet. I have different products and for each of them I would like a new file. Is it possible to split the sheet by some rule?
P.S. I am new in here, so if my question is ...
1
vote
1answer
163 views
Open source alternative for working with large xlsx files [closed]
I am new and I hope the this is the right place for the following question:
I would like to collaborate with people using large xlsx files (many rows).
Which open source alternative can handle these ...
0
votes
1answer
54 views
Get frequency distribution of several columns partitioned by one other column
I'm doing some basic survey analysis. I want to break down responses to various questions based on Male or Female. Each row is a response, each column is a question. Column A has the Male/Female ...
0
votes
0answers
23 views
OpenOffice fork that allows debug.print in VBA
Is there a version of OpenOffice/LibreOffice/Symphony, or an addin, that supports a version of VBA that allows 'debug.print' statements?
0
votes
0answers
200 views
OpenOffice Calc to PDF: How to export table without page borders?
I would like to export an OpenOffice Calc table as a PDF. The PDF will later only be used digitally. Therefore I'd like the PDF to be only the table, no white space around it, etc.
The normal way to ...
1
vote
0answers
93 views
In Open Calc, how do I copy a paste cells that contain references to other workbooks?
I have an OpenOffice Calc sheet with cells containing formulas that refer to another file, e.g: 'file:///C:/path/to/other.xls'#$Sheet1.A1:B2.
When I copy the cells from one sheet to another in the ...
0
votes
1answer
190 views
Spreadsheet VLOOKUP: Search in second column and return first
In Excel or OpenOffice calc I have
| Foo | Bar |
+-----+-----+
| A | 1 |
| B | 2 |
Is it possible to use VLOOKUP to search a value in the Bar column and return the value in the Foo column?
0
votes
1answer
100 views
Where is openoffice.org add-on located after installed (Ubuntu)?
I installed a tool which now appears in Openoffice Calc under Tools->Add-ons.
I thought that this tool would appear in ~/.openoffice.org/3/user directory, but I cannot find it.
I am especially ...
0
votes
1answer
429 views
Open Office crashes, recovers, crashes again
After completely reinstalling my laptop due to apparent registry corruption, I've encountered a problem with Open Office:
I open a simple Calc spreadsheet, it comes up normally, but then after ...
0
votes
0answers
91 views
How to create a excel spreadsheet that measures bed cleaning turnover? [closed]
Currently working a healthcare facility that does use a bed tracking system. I want to submit a monthly report that will tell the time in minutes it takes to clean a patient room. (turnover a room ...
1
vote
1answer
73 views
OpenOffice Calc - getting ### as the result to a formula
So I've got a spreadsheet with around 5 different rows, each one with a specific value (specifically a range of cells E2 - E8) and a "Total: " cell at the bottom, whose formula is this:
=Sum(E2:E8)
...
-1
votes
1answer
472 views
How to make LibreOffice Calc to connect points in a holed series chart with lines?
I've got a time series data table with one function (row) value available for every month and the second for every 3rd month (every quarter), having holes (empty cell values) for every 2nd and 1st ...
3
votes
3answers
641 views
Finding min/max values in column where cell matches something
I have a spreadsheet laid out kind of like this:
Date Project#
2012-01-01 130
2012-01-02 153
2012-01-03 153
2012-01-04 130
2012-01-05 130
2012-01-06 130
And I want to get ...
2
votes
0answers
143 views
OpenOffice Calc weighted average
I need to find the result of
((C1 * 5) + (C2 * 4) + (C3 * 3) + (C4 * 2) + (C5 * 1))/(5*6/2).
I need to do it for 200 columns, not only 5. I currently use two sheets with sheet 2 containing a ...
2
votes
2answers
151 views
Modify value of a cell using the old value as part of the new one?
I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below.
How can I add modify the cells ...
