# Tagged Questions

**-1**

votes

**1**answer

23 views

### Excel: Shortcut key to move selection to specific row

I am creating a bot where I need to move the selection (cursor) to a specific row, like row 1, or row 5. Does such shortcut feature exist or is it possible to create it?
Alternatively, does ...

**-5**

votes

**2**answers

123 views

### How do I convert a .csv file to a database?

I have an 8GB .csv file with 6 columns and 202,176,000 rows.
How would I convert that to an OpenOffice database(.db) file?

**2**

votes

**2**answers

52 views

### How to autofilter for given strings in common spreadsheet softwares?

I know I can create an "autofilter" at the top of an XLS table.. But how can I create a filter that filters for given strings?
example TABLE input:
LASFD@FOO.COM
LKXYCVER@FOO.COM
...

**0**

votes

**0**answers

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

**0**answers

67 views

### Excel or OpenOffice Table Summary: how to reconstruct a table from another, with “missing” values

I have a table of values (partial) with 3 columns: month (from 1 to 12), code and value. E.g.,
MONTH | CODE | VALUE
1 | aaa | 111
1 | bbb | 222
1 | ccc | 333
2 | aaa | 1111
2 ...

**3**

votes

**3**answers

167 views

### How do I convert a delimited list to a multicolumn table in excel?

I have a list like this:
Friut;Taste;Color;Other;Apple;Good;Red and Blue;1;Orange;Really Good;Orange;12
And I want to convert it by selecting every 4 delimitations and turning them into rows like ...

**1**

vote

**0**answers

313 views

### Why recovered .xls and .doc files show as symbols?

I formatted an NTFS drive (and re-installed windows) which had some important .doc and .xls files, I used 2 recovery software and both recovered my files but seem to be corrupt, when opened they ...

**0**

votes

**1**answer

402 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

**1**answer

42 views

### Excel/Calc: Pivot table with (almost) identical row: how to identify them?

I need to examine via pivot a datasource from a sheet. This datasource sheet is something like this:
| Field 1 | Field 1 | Field 3 |
| AAAA | BBBB | CCCC |
| DDDD | EEEE | FFFF |
| GGGG ...

**0**

votes

**1**answer

853 views

### Excel/OOCalc: finding rows that have matching values in one field but different values in another

I have a large table (20-30 cols, 10-15K rows). What I need to do is to find the number of rows that match identically in one (or two) particular field(s), but not in another specific field (rest of ...

**0**

votes

**3**answers

180 views

### Multiple “If” answers in one cell

I am trying to make a fishing tournament spreadsheet.... I would like to perform in once cell that if their position was first, they get 100 points. if their position is second, they get 90 points, ...

**0**

votes

**0**answers

160 views

### Excel/OpenOffice spreadsheet template for expected and actual values

I would like to create a spreadsheet to generate graphs about cost items (anything with a cost, doesn't matter what) in order to have:
expected values: these would be created using some formulas ...

**1**

vote

**1**answer

80 views

### excel / open office - append an incrementing value to all non-unique fields

I have a large table of about 7500 store names. I need to search through those names and, if they are not unique, append an incrementing value, for example:
store_1
store_2
etc.
Anyone know how to ...

**2**

votes

**1**answer

104 views

### Tagging or defining automatically in an Excel or Libre office spreadsheet?

I want to review and sort my expenditures in a spreadsheet where I have all my transactions listed. Is there a way to automatically label or tag to categorize the transactions like in a budget ...

**0**

votes

**1**answer

924 views

### Set row delimiter in Excel

How can I use row delimiter other than \n?
CSV fields contain \n and the row delimiter is \n\n\n\n, to separate line breaks in fields from the row delimiter.
I'm using Excel 2010, and in need to ...

**0**

votes

**1**answer

103 views

### Referncing a cell, based on a cell's value in Spreadsheet

I understand how referencing a cell works, example being
=Sheet1.A1
However, if I'm in Sheet2 and cell B1 contains Sheet1, and cell C1 contains A1.
Is there any way to write the reference such ...

**2**

votes

**1**answer

929 views

### OpenOffice: Replace text with new-line

In my OpenOffice spreadsheet, there are cells containing "\n" text.
Each "\n" occurrence needs to be replaced with a new line or line break.
The Find-and-Replace dialog does offer regular ...

**5**

votes

**4**answers

55k views

### How to open ods files in Excel?

What is the best way to open ods files in Excel 2003?
Is there some plug-in available? Should I use some convertor to xls? Or would it be best if I just used LibreOffice (or OpenOffice)?
The file I ...

**1**

vote

**3**answers

1k views

### Convert ZIP code to state in OpenOffice

I'm trying to move data from two of our old systems into a new system. Problem is, somewhere along the line the 'state' column of our DB was corrupted. I have a column of ZIP codes I need to ...

**1**

vote

**1**answer

4k 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 ...

**2**

votes

**3**answers

1k views

### Excel / open/libre office formulae: number of full months left until date

I need a formula which equates to an integer value for the number months left until a set date (2011-10-16) based on the current date.
e.g.
on 2011-03-13 the answer would equate to 8
on 2011-03-17 ...

**2**

votes

**2**answers

2k views

### Database access through odbc driver and excel

Is it possible to access a database via ODBC and access this in MS Excel or OpenOffice, then exporting the database into a flat file?
The program I am accessing data only allows an ODBC connection ...

**2**

votes

**1**answer

102 views

### Repeat rows at top of printed page

In Excel, under Page Setup, there is an option for "Print Titles", where you can specify a number of rows to repeat at the top of the printed page, as well as a number of columns to repeat at the left ...

**0**

votes

**2**answers

657 views

### Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range?
I know that min() would give you the lowest value but i need something to return the 100 lowest values.
I just used 100 ...

**3**

votes

**1**answer

3k views

### Ubuntu: Convert OpenOffice Calc to Excel workbook using CLI

I need to create an automated report in a spreadsheet format. Unfortunately, There seems to be an easy way to create these reports using OpenOffice Calc, but upper managements wants them in MS Excel ...

**3**

votes

**3**answers

5k views

### How to create diagram in spreadsheet with dates on x-axis?

I recently became a father to twin boys. Naturally there is a lot of weighing and measuring, and we record these numbers in a spreadsheet. I use OpenOffice.org Calc (but I'm open to solutions for ...

**2**

votes

**5**answers

5k views

### In a spreadsheet (Excel, Google Docs, OpenOffice), how do I transpose data from rows to columns, but only non-null cells?

I work with all three spreadsheet programs listed above, and I believe my question can be answered the same or similarly for all three.
On my sheet, I have a top row full of column headers. The ...

**4**

votes

**4**answers

2k views

### Openoffice.org: Mouse wheel one row at a time possible?

I noticed this in Excel, now in OpenOffice.org calc. One small change in mouse wheel leads to 3 rows (line) change. Is it possible to change in Calc OR/AND in Excel?
EDIT: Yes, I know about system ...

**0**

votes

**5**answers

4k views

### I have a spreadsheet were lots of cells have the character ' in the first position of each value, how to remove it?

Hi want to format all of the values in a column to be a phone number, but the first character in many cells is
'
This prevents the cells from being recognized as a number. I can't do a search ...

**0**

votes

**1**answer

2k views

### How to dynamically highlight the minimum value in Excel or OpenOffice.org Calc?

I am using Microsoft spreadsheet to record the numeric values in columns.
I would like to compute the minimum of the row and want to highlight it when the cursor moves to next row.
Could you kindly ...

**4**

votes

**1**answer

223 views

### Is it Possible to not copy all 65,537 cells when copying a column in Open Office

I'm using Open Office 3.01 on OS X. When I select an entire column of data by clicking its header, and the copy that to the clipboard, Open Office appears to copy all 65,537 cells to the clipboard. ...

**1**

vote

**3**answers

2k views

### How can I use the Excel PMT function to calculate a mortgage payment?

The formula wizard asks for:
Rate, NPER, PV, and FV.
I have the following info:
I guess the rate would be the annual interest rate (say 5.5%) divided by 12, or 0.48%.
NPER is the number of ...

**3**

votes

**4**answers

3k views

### How can I copy a formula multiple times and always refer to the same cell?

I have a number in cell A1 and a column of 20 numbers in the B column.
I'd like to put a formula in each of the cells in the C column next to each number in the B column. Let's say it's =B1-A1, and ...