0
votes
0answers
10 views

OO Calc: Detecting Blank Cells and Replacing with Cell above?

So annoyed I can't find a simple answer to this simple question! I'm using OpenOffice Calc. I want a formula which will detect if the cell is blank, and if it is to replace it with the contents in ...
4
votes
1answer
14 views

Removing functions from a column, but keeping data (convert to normal text)?

I have a column that contains the results of a few different formulas. I want to convert all of these cells to normal text, getting rid of the formulas. I know I'm missing something obvious here.
1
vote
0answers
9 views

OpenOffice doesn't copy charts properly

I'm using OpenOffice 4.0.0 and I have charts in them. Whenever I try to copy and paste them into another program it just copies and pastes a white rectangle (of the same size as the chart). I've tried ...
1
vote
1answer
24 views

spreadsheet: sorting by the first n characters of the entries of a column then by another column

Is there any way to sort an OpenOffice Calc spreadsheet by the first n characters of a given column then by another criterion? For example suppose that we have the following 4*2 spreadsheet (Note ...
0
votes
0answers
12 views

How to accept LibreOffice Calc autocomplete suggestion without leaving the cell edit mode?

Whenever I start typing a sentence in a LibreOfice Calc cell it suggests autocomplete options based on other cells contents. I usually would like to accept it and make a slight modification but when I ...
1
vote
2answers
92 views

Filter OpenOffice Calc rows by matching if a cell in one column exists in another column with a certain value

Have a question I cant seem to figure out using the filter. Column A has 1400 rows with 800 additional rows that are duplicates (so 2200 total rows). Column B has inventory amount for only the last ...
-1
votes
1answer
119 views

Change background fill of cells in LibreOffice worksheet containing specific text

How can I change the background fill of all the cells of a LibreOffice spreadsheet worksheet that contain a specified text? In Excel that could be done by configuring the settings in the find and ...
1
vote
0answers
98 views

Default font and cell padding when opening files in LibreOffice/OpenOffice Calc

I've seen solutions for setting a default font when creating new files. What about a default font (and cellpadding) when opening an existing CSV file? LibreOffice seems to always default to Liberation ...
0
votes
0answers
72 views

Disable prompt when opening a CSV file in LibreOffice/OpenOffice Calc

Every time I open a CSV file, LibreOffice presents me with a prompt asking to confirm Character Set, Language, and Separator options (tab, comma, semicolon etc). This prompt is not present in ...
0
votes
1answer
141 views

Libre Calc error on a float in decimal notation. Err. 519 (#value error)

There is a problem with my libreoffice. I doesn't calculate cells with decimal points, possibly seeing them as text. I have checked my locale settings (you can see them in the picture above) that ...
2
votes
0answers
172 views

Is it possible to remove the hyperlink background in OO Calc?

Hyperlinks in OpenOffice Calc are displayed with a solid background which covers the background color of the cell. I don't want a solid background; I want the background color of the cell to show ...
0
votes
0answers
30 views

how can I merge in master record details to each row in excel or openoffice?

Say I have sheet 1 with: company1 90230 CA company2 80014 CO company3 15090 PA And sheet 2 with: company1 Bob Smith company1 Sue Smith company3 John Doe How can I produce sheet 3 with: company1 ...
1
vote
2answers
778 views

Move rows with the keyboard in Calc

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this. Note that I am not looking to ...
3
votes
1answer
291 views

Is there a way to set the default quotation mark in LibreOffice Calc from smart quotes (“ ”) to dumb quotes (the straight kind)?

I use LibreOffice Calc to make lists of programs with different parameters to run on an hpc cluster. For each batch of commands I must do a find and replace on the smart quotes, as they cause errors ...
1
vote
0answers
100 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
0
votes
1answer
91 views

Open Office Calc truncates my data on paste, how do I fix this?

Just as the title says, I am using Open Office Calc as a flat file to upload data. One column is my description and therefore can be a bit lengthy. Unfortunately it truncates this text. Anyway to ...
1
vote
1answer
88 views

Is there another way to display a spreadsheet within OpenOffice Writer besides an OLE object?

I'm writing a paper for college in which I would like to either display or link to a large spreadsheet. I'm using OpenOffice Writer and Calc. I've tried importing the spreadsheet as an OLE object, ...
0
votes
1answer
39 views

How do I generate a column of dates.

I want to generate a column of dates that are staggered by one month like this: 7/01/13 8/01/13 9/01/13 Right now what I've tried is dragging down the black dot in the lower right hand corner of ...
1
vote
2answers
380 views

How do I drag down complex calculations in open office.

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks ...
1
vote
1answer
122 views

How does Open Office Calc make this conversion?

In a column, I have dates of the format 2013-05-27. When I selected "Default Formatting", the dates got changed to five digits, for example, 41422. Where does this number come from? It's not even ...
0
votes
0answers
127 views

enteries in column are automatically changed to upper case letters

I am very new with Open Office and spread sheets in general. In a column whenever I add a new entry it changes all the letters to upper case. Is there a way to disable this? If not, many entries ...
0
votes
1answer
98 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
1answer
435 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
436 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 ...
1
vote
1answer
387 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 ...
2
votes
2answers
165 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 ...
1
vote
1answer
62 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
552 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
2answers
63 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
170 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 ...
2
votes
0answers
427 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
1k views

OpenOffice Calc - Highlighting the higest value in a column

So I've got this spreadsheet open using OpenOffice Calc (ver 3.3.0) and its set up a little like this: A B C D 1.name quantity price total 2.foo 10 ...
2
votes
1answer
4k views

Open Office Calc: Convert a date to timestamp

I have a column with dates in M/D/Y like this: 8/8/2007 8/9/2007 8/10/2007 8/13/2007 8/14/2007 8/15/2007 8/16/2007 I would like to convert them to a unix timestamp (in a new column). Would anyone ...
0
votes
1answer
3k views

OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1. =IF(A1>B1;"A";"B") will display A in ...
2
votes
1answer
687 views

How can I bypass the import dialog when opening CSV with OpenOffice?

A (Python, but that's not really relevant) script I'm writing will issue a command line call to open a CSV file with OpenOffice (OO). I need OO to skip over the import dialog box while opening the ...
1
vote
1answer
4k views

How do I create a line graph which ignores zero values?

I want to create a graph showing trends over time, calculated from a formula of various data. However, some data is missing, resulting in zeroes in the calculated data which the chart uses. When ...
3
votes
1answer
2k views

How do I make a random selection in a spreadsheet using more than one criteria

I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...
0
votes
1answer
291 views

OpenOffice Calc getting slower and slower

I am running OpenOffice 3.2.0 Calc on my Ubuntu (10.04) desktop. I have a huge data entry task to complete today and Calc is getting slower and slower moving between cells on the sheet. When I rename ...
1
vote
0answers
508 views

Copy “true” value from OpenOffice (for pasting in external app)

Is there some way to get OpenOffice Calc to copy the real value stored/calculated in a cell. I realize it must still be text, but is there some way to get OpenOffice to copy a higher precision value ...
2
votes
1answer
558 views

Copy only cell content in OOCalc

In OOCalc, when I copy a cell with ctrl-c ctrl-v, it copies everything, also the style, from source to destination. However, I want to copy only the content, leaving the formatting of the destination ...
4
votes
2answers
696 views

How can I split a formula into multiple lines in OpenOffice calc?

I have this simple formula: =CONCATENATE("foo";"bar") which renders foobar on the cell. How can I lay this formula in multiple lines in the same cell? I'd like to be able to do something like the ...
1
vote
1answer
5k views

How to conditionally highlight cells in OpenOffice Calc?

I have a big chunk of data in an OpenOffice spreadsheet. To make real information standout more, I would like cells with an absolute value over a certain threshold to be highlighted in red. Is there ...
0
votes
1answer
419 views

Count the number of contiguous empty cells at the end of a row

I have rows of data, some cells are empty and some contain a number. I need to identify how many contiguous empty cells exist at the end of each row. I've tried using a string of nested IF ...
7
votes
1answer
1k views

OpenOffice.org calc: how to dynamically update a data range for a graph?

I have an OpenOffice.org spreadsheet that contains a number of graphs. As I append more data to this spreadsheet, I want to update all the data ranges in those graphs, basically changing A1:A100 to ...
4
votes
4answers
832 views

Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...
12
votes
3answers
19k views

OpenOffice: get distinct values from column

How do I get a list of all distinct values from a column of values? Basically, this question: Excel: Get distinct values in column but I need the answer for Open Office Calc instead of MS Excel. ...
0
votes
2answers
4k views

OpenOffice ruined my CSV file - (how to save CSV on Linux with OpenOffice?)

I am running OpenOffice 3.2 on Ubuntu. I had a CSV (comma separated value) file which I had manually created. I opened it in OpenOffice, and entered about 100 items (to make use of using formulas to ...
1
vote
0answers
408 views

Split/Count comma delimited string in OpenCalc

I have a cell that is formatted like so : Jill,Bob,Stan etc. Is there a way to count the number of name present in the string?
0
votes
2answers
419 views

Is there a way to add times that total greater than 24 hrs?

I'm trying to add a sum of cells with the format HH:MM. If the sum exceeds 24h I get strange results like 3:55h I tried [H]:MM:S Which format should I choose? I'm using open-office calc.
0
votes
3answers
337 views

Opening OpenOffice.org documents is slow on Windows 2003 server

While trying to open/access OpenOffice documents on Windows 2003 server it gives a very slow response, while accessing the same document using Microsoft Office 2007 it opens up instantly. The version ...