Questions tagged [openoffice-calc]
OpenOffice's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively
81
questions with no upvoted or accepted answers
7
votes
0
answers
1k
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 ...
- 589
4
votes
0
answers
238
views
In LibreOffice Calc, how can I change the cell selection black ring to something lighter for dark backgrounds?
In LibreOffice Calc, how can I change the cell selection black ring to something lighter so it can be easier to see for dark backgrounds? I will attach an image to show what I mean. It would be nice ...
- 277
3
votes
1
answer
83
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 =...
- 233
3
votes
0
answers
5k
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 ...
- 443
3
votes
3
answers
4k
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 ...
- 293
3
votes
1
answer
2k
views
Open Source Spreadsheet with Sensitivity Analysis
Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...
2
votes
0
answers
882
views
Dropdown Lists in both Excel and Open Office
I have an Excel workbook which works as a time sheet. Users select the project they worked on and how many hours they spent on it that week. The project can be selected from a dropdown list showing ...
- 21
2
votes
0
answers
145
views
Does LibreOffice Calc calculation time out or have any limits?
I have a relatively large spreadsheet (2300 rows, out to column EL) and lately the auto calculate has been unreliable. I would have to click on a cell I was sure was incorrect, hit F9 and see its ...
- 678
2
votes
1
answer
260
views
LibreOffice Calc paste as if always into formula bar
I am forever frustrated with how cut and paste works in LibreOffice.
For example, I find that phone numbers are treated as numeric and leading zeros go missing, or plain text gets pasted with font ...
- 2,148
2
votes
0
answers
801
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 ...
- 2,948
2
votes
0
answers
218
views
How do I capture filter information from a DataPilot table?
In OpenOffice Calc, how do I capture filter information from a DataPilot table?
Specifically, I have a DataPilot table which I graph on a chart, filtering on a field not appearing in the table.
I ...
- 225
2
votes
1
answer
615
views
Are there nonlinear optimization implementation for OpenOffice Calc?
Are there nonlinear optimization implementation for OpenOffice Calc?
I found one: http://extensions.services.openoffice.org/project/NLPSolver . But the methods provided there are evolutionary methods,...
- 17.3k
1
vote
0
answers
89
views
Is there a way to find the two smallest numbers in a column in openoffice without repeats
The obvious but incorrect answer would be Small(Range;2) except that if there is more than 1 cell with the smallest value then it will return the smallest value instead of the 2nd smallest value.
...
- 21
1
vote
1
answer
204
views
Calc/Excel aggregating data with same ID
Hello i have to solve this problem in Libreoffice Writer or excel.
I have a table like this:
ID V1 V2 V3 ...
1 10 20 30 ...
1 30 40 50 ...
1 5 7 8 ...
.. .. .. ....
- 11
1
vote
0
answers
106
views
OpenOffice Calc: Is it possible to create conditional formatting to highlight a row based on checkmarks?
This is kind of a two-part question.
Is it possible to create conditional formatting to highlight an entire row with a certain color based on whether the checkmark object in the row is selected? For ...
1
vote
1
answer
4k
views
How can I add duplicate rows to LibreOffice Calc to make there be one per day?
I have some data like this (it's stock market data)
29/07/2015 35972.82 32056.2 17751.39
30/07/2015 35961.86 32046.43 17745.98
31/07/2015 35848.13 31945.08 17689.86
03/08/2015 ...
- 2,148
1
vote
1
answer
1k
views
How do I lock some columns and some rows in a Libreoffice Calc worksheet?
I am using Libreoffice Calc 5.2.0.4.
I am building an appointment book. This book has many sheets/ pages.
I am not experienced, so I was experimenting on different sheets. And now I have three ...
- 31
1
vote
0
answers
125
views
Alternate row shading by ID - Conditional Formatting in OpenOffice
02902982085 4 a ? <-- yellow
02902982085 # 1 r <-- yellow
02902982085 b $ 0 <-- yellow
01395235224 w z [ <-- blue
01395235224 a - 5 <-- blue
...
- 11
1
vote
1
answer
1k
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 ...
- 111
1
vote
0
answers
287
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,986
1
vote
0
answers
1k
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 data)
...
- 185
1
vote
0
answers
48
views
How to pull all rows containing the same date into a new sheet in OpenOffice Calc
I have 15 spreadsheets (one for each client); each has a column for the date and then additional information in the other columns such as 'action' and 'time spent'. This allows me to see all the work ...
- 11
1
vote
1
answer
99
views
calc: Extract string from range of cells
I´m trying to figure out a easy to understand way to extract a string (that is in a range of cells) from a range of cells...
That is the range of cells I have to look for a specific string (in a ...
1
vote
0
answers
60
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 ...
- 8,229
1
vote
0
answers
310
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 ...
- 11
1
vote
0
answers
714
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 ...
- 11
1
vote
1
answer
340
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 ...
- 135
1
vote
0
answers
75
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 ...
- 11
1
vote
0
answers
383
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 ...
- 171
1
vote
0
answers
201
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 ...
- 364
1
vote
1
answer
591
views
Delete cell content in Libre (Open) Office based on the cell value
I have a huge csv file (tens of thousands of rows) that I need to filter based on different criteria. After trying to find a proper CSV editor, I decided to use LibreOffice Calc. CSVed is great, but ...
- 111
1
vote
1
answer
1k
views
Drop down List Not working in Open Office
i have created a file using MS Excel and saved the file as ODS but it will open on read only mode works perfectly but once i saved the file drop down list not working
1
vote
0
answers
1k
views
OpenOffice Calc: How can I count the number of different items with data pilot?
I have a rather long spreadsheet with historical information of issues solved by some user on a collaborative environment.
The spreadsheet have the following (relevant) columns
date, week no., ...
- 263
1
vote
1
answer
3k
views
How to convert text to decimal in openoffice calc?
I have a column which contains:
€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40
and so on. I want to sum the column up, but for openoffice it is text and not a decimal.
What I did:
...
- 258
1
vote
0
answers
536
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?
- 111
1
vote
3
answers
576
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 ...
- 333
1
vote
1
answer
813
views
Why does autocomplete in Open Office Calc not work for older entries?
I have a number of words in a Calc column that I update occasionally. When I type a new word in a cell, it will autocomplete if I already have one higher up in the column. This is useful so I don't ...
Mark
0
votes
0
answers
61
views
Create Binary Search in LibreOffice Calc?
Does anybody know of a way to organize a binary search in LibreOffice Calc?
To take a simple example, say I have column A with the numbers 1-16 listed, sorted in ascending order: (1,2,3,...); and ...
0
votes
1
answer
137
views
How can I copy and paste a string of characters into libreoffice calc with one character per cell?
ACDAEACCDDAEFFAFCCDFABAADACCCDDAFEDGABEGFCADEHBHACAGHACDEDECDAFAFAFCACDCDEAFCDFCGHDDFF
1 ACD.E..........F......................................................................
2 ...A...CD...F...........
- 244
0
votes
0
answers
614
views
Character encoding in LibreOffice Calc of an existing XLW file
I have an existing .XLW file (created in the early 1990's with Microsoft Excel). LibreOffice Calc (7.2.7.2) opens it and displays it very nicely.
However, since it contains characters in a non-...
- 237
0
votes
1
answer
488
views
Sort LibreOffice Calc rows through all columns
I have a list of items with set of properties. Some of the properties are unique, some are the same. I want to sort the properties through all items to see which items have equal properties.
Maybe it'...
- 1
0
votes
2
answers
2k
views
Why does index match get wrong result in Microsoft Excel?
The formula that I use is
=INDEX(F:F,MATCH(K2,A:A)*(B:B=L2),0)
- 1
0
votes
0
answers
20
views
Open office Assign a number to a color
What I'm trying to do is calculate the number of hours worked according to the color and the person, all of this to be determined in the nombre d'heures column in front of each person.
Knowing that ...
- 1
0
votes
0
answers
189
views
grep-like filter in office suites (OpenOffice)
The GNU grep command allows to search inside the incoming stream of data, for example inside the files. It have two similar and very useful CLI parameters: -A & -B:
-Annn shows nnn lines following ...
- 101
0
votes
1
answer
322
views
Automatically reference to a cell on other sheets
I have a libre calc file with many sheets and I want the first sheet to contain a list with the other sheets names and next to each one the contents of a specific shell of every one of those sheets.
e....
0
votes
1
answer
871
views
Retrieve European Central Bank currency rates XML into LibreOffice Calc cells
The premise is to retrieve current currency rates into a Libre/OpenOffice Calc spreadsheet. Previously I did it using a JSON service, with manual tweaking of substrings, which isn't all that user-...
- 779
0
votes
0
answers
743
views
How to set the color of the input bar in LibreOffice Calc spreadsheets (for dark theme/mode)?
In LibreOffice Calc, I have been able to almost configure a usable dark theme/mode.
To accomplish this, go to Tools > Options... > LibreOffice > Personalization and set the dark theme, and ...
- 8,328
0
votes
0
answers
42
views
Can OpenOffice Calc AutoInput reference data on a different sheet?
I've set up a spreadsheet in OpenOffice for project estimation at a contractor. The first column is a description of the item or service that I'm adding into the price. Since there are many commonly ...
0
votes
2
answers
586
views
Calc print several sheets in same page
I have a Libreoffice Calc spreadsheet that holds around fifty sheets (each one with no more than a dozen valued rows); this is good for data organization — each sheet holds a sequence of events ...
- 225
0
votes
1
answer
401
views
Summing Euro amounts in Libreoffice calc
I have a spreadsheet with a column full of Euro amounts. This spreadsheet is created from a CSV and the amounts are interpreted as strings (text) rather than as numbers.
I've been trying to get the ...
- 118