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

learn more… | top users | synonyms

1
vote
2answers
24 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 ...
2
votes
1answer
79 views

Convert bidimensional table to a single one

I have some data with product prices relative to tariff code : +==============+========+========+========+========+ | product code | code10 | code20 | code30 | code40 | ...
0
votes
1answer
24 views

How to select a random item in list not flagged in second column?

So, I watch movies, and I have 177 of them, I put all the titles into the A column, and Figured out how to do the Randomization to bring up the titles, but I want to have a column, also for it being ...
1
vote
1answer
14 views

How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape. This can be done in Excel, but in OpenOffice when i set ...
1
vote
2answers
31 views

How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
0
votes
0answers
8 views

How do I change the EOL character when importing a CSV file into Open Office Calc?

I've got a CSV file that has enclosed newline (\n and \r, mixed) characters in its data. Because the consumer of that file is not able to handle that correctly (out of my control) we decided to use ...
6
votes
2answers
186 views

OpenOffice Calc: reduce file size when saving as Excel

I'm working on a spreadsheet together with another person, sending new versions forth and back by email. It contains nothing fancy. While he uses Windows I'm on Ubuntu 12.04.4 LTS. The first version ...
3
votes
2answers
32 views

Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?

We have lots of spreadsheets that have lists of numbered sound files for an educational app. We use spreadsheet features such as "fill down" for the numbering, and we often use formulas to check ...
1
vote
1answer
13 views

Numbering every third row in OpenOffice Calc

I am building a list of proofreading corrections in which the first row identifies the page, paragraph and line number for correction The second row identifies the OLD (existing sentence) The third ...
1
vote
1answer
17 views

Date Arithmetic in Spreadsheets

I have a bunch of data in a spreadsheet where the left-most column represents a date in the format DD-MM. Here's an example for column A: 15-09 23-09 25-09 30-09 02-10 I ...
3
votes
2answers
41 views

Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC). Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded ...
0
votes
0answers
37 views

How to format pennies as dollars in LIbreOffice currency formatting?

I have data in a LibreOffice Calc spreadsheet with penny values, such as 001234 to represent $12.34 (twelve dollars and thirty-four cents). How can I define a format that will display these values as ...
1
vote
0answers
34 views

How to combine data from multiple sheets into 1 main sheet in calc

I have an excel file which gets generated from various database servers on fly by executing sql. Separate sheet gets generated for each server. Now I simply want to combine data of all sheets. Each ...
1
vote
1answer
72 views

Changing the units on the x-axis in a LibreOffice Calc chart

I've used the LibreOffice chart wizard to make a chart, and I would like the x-values to be different to the default of 1,2,3... (in this case, 500,1000,...). How can I do this?
1
vote
1answer
38 views

LibreOffice calc: How to apply multiple tables to a single chart

I'll say it right off the bat - I am a complete Excel/Calc amateur. What I want to do is apply 3 tables, each with 2 columns of data into a chart with 1 XY-line for each. My current data looks ...
0
votes
0answers
26 views

OpenOffice Calc convert with command line

I am trying to convert xls to dbf with OpenOffice Calc via command line (Windows), but I'm getting an error with this line (path is set): scalc --invisible -convert-to dbf c:\MATIKGL.xls Error : ...
1
vote
2answers
180 views

Interpolate values between two cells

I've got a spreadsheet that looks like this in Libre Office Calc: +-----------+ | | A | | 1 | 444 | | 2 | | | 3 | | | 4 | | | 5 | | | ...
1
vote
2answers
36 views

What's the best way to cut out all text before or after a matching string with excel or open office?

I have a column of naughty data, that needs to be standardized. Its a single column, so I'll just include a snippet of it it here: pop_density_2010 7002854600000000000854.6/sq mi (329.97/km2) ...
0
votes
1answer
23 views

Is there a way to use multi-character delimiters in Open Office Calc CSV file format?

Is there a way to save a CSV file with multi-character delimiters? When I enter more than one character, it just uses the first character.
-1
votes
1answer
33 views

How to add the current page number in a cell of a calc sheet

I would like to add the current page number in a cell of that page and to refer back this cell for this page number in a cell somewhere in the same sheet / in another sheet. Is there a way to do so?
-1
votes
1answer
94 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
1answer
87 views

Applying Conditional Formatting to all computed values in LibreOffice Calc

In LibreOffice Calc, how can I use the Conditional Formatting feature to apply a style to all cells which contain a computed value (via formula, as opposed to a directly input value)? LibreOffice ...
1
vote
0answers
62 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
54 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 ...
1
vote
1answer
158 views

LibreOffice pivot: How to count 1 per line?

I have a list of fruit occurrences like this: banana banana apple orange apple And I want LibreOffice to give me a number of occurrences per fruit, for instance: banana | 2 apple | 2 orange | 1 ...
0
votes
0answers
56 views

OpenOffice Calc with csv file produces Err:520

I'm loading a csv file into OpenOffice Calc (version 4.0.1) and in only 1 column I get 'Err:520' as the cell value. I checked under Tools->Options->OpenOffice Calc->View->Display to check whether ...
0
votes
1answer
101 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 ...
0
votes
1answer
115 views

How do I sum across a list of columns defined in an extra column or cell in excel

Suppose columns A, B and C contain numbers. I want to sum everything in any of these columns if they are listed in an extra column or cell. The columns to be summed can be anywhere in the spreadsheet. ...
2
votes
0answers
128 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 ...
0
votes
1answer
1k views

How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...
0
votes
1answer
69 views

Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...
0
votes
0answers
51 views

Macro to manage sport ranking and calendar?

I need to write a macro (or a different easy way) to manage ranking and calendar for curling tournament. The event will follow the Shenkel system first match determined by general draw after that ...
1
vote
2answers
505 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
206 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 ...
0
votes
1answer
67 views

Conditonal sum in Excel

I would like to write the formula (in OpenOffice, but I imagine it won't be any different than Excel) to perform the following calculation: -> Go through the values in column B from B1 to B30 and add ...
0
votes
0answers
61 views

How to dynamically reference another sheet when copying formulae (OO Calc)?

I have a cell that contain sheet's name (N1). I wrote a formula that uses cells in the sheet pointed to by N1. Is it possible to copy this formula so that the cell references it contains (to the ...
1
vote
0answers
88 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
0answers
48 views

OpenOffice - Save all documents as HTML using macro

I am newbie to Open Office Macro. I have a requirement of Save documents(PPT,PDF,XLS,DOC) as HTML format using macro code. Is it possible to save documents as HTML by macro recording in open ...
1
vote
0answers
192 views

OpenOffice Basic Change BackGround Color for Range Based on Text in Column 0

I'm trying to test each cell in column 0 "A" to see if it has specific text "Budget" "Living" "Savings" and if it does then change the row background color to a specific color for each. I am able to ...
0
votes
1answer
75 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
73 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, ...
1
vote
1answer
156 views

Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc? To clarify, can I set a cell's formula to something like this: =B3*C4-system("/my/amazing/script ...
3
votes
1answer
126 views

Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value A1:"AB 0123" and in cell B1 I need the same value with a line break instead of the space like B1:"AB\n0123". How can I archive this? I ...
2
votes
0answers
1k views

Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with the font color red. All other rows are in the default font color. Can I use the font color of a cell within a formula to react to ...
0
votes
1answer
351 views

Openoffice Calc - Quickly count marked lines when filter is active

I need help counting lines. When I set a filter (AutoFilter) in Calc, rows that are filtered are hidden, but all rows keep their row number. How can I see quickly, how many lines are currently ...
0
votes
1answer
35 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
337 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
117 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
113 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 ...