# Tagged Questions

**1**

vote

**2**answers

82 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

**0**answers

19 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

**2**answers

265 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 ...

**1**

vote

**2**answers

44 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

**1**answer

165 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. ...

**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

**1**answer

77 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

**0**answers

56 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 ...

**0**

votes

**1**answer

71 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

**1**answer

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

**1**answer

186 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 ...

**0**

votes

**3**answers

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

**1**

vote

**1**answer

995 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?

**2**

votes

**2**answers

425 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 ...

**3**

votes

**2**answers

1k views

### Excel/Calc/Whatever: Flip my axes - ie reflect my table across the diagonal

Lets say I have a table and I want it flipped across the diagonal. So instead of X by Y its Y by X...
Before:
A B C
RED 1 2 3
BLUE 4 5 6
...

**1**

vote

**2**answers

12k views

### Pull row of data from one place in spreadsheet to another

I have a list of names and their assorted properties.
I am trying to find a way to quickly move an entire row's worth of data into a different sheet so I can manipulate it.
Currently I can ...

**13**

votes

**10**answers

23k views

### Export Excel graphs as vector graphics files (e.g. SVGs)?

How can I export a graph generated in Excel as an editable vector graphics file, e.g. a SVG?
When I right click on a graph in Excel the 'Save as Picture...' option only gives non-vector formats (like ...

**0**

votes

**2**answers

607 views

### Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.
I have a multiset of data described by rows of (count, value) pairs. ...

**1**

vote

**1**answer

877 views

### Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...

**0**

votes

**1**answer

915 views

### how to find cells that have a certain text in open office's calc (excel might work also)

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this:
- get all cells that have the text "electric bill"
- get the cell at the right of that one, ...

**3**

votes

**1**answer

1k views

### How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.
In the game your character have 9 "slots" for weapons and armors, 1 for ...

**2**

votes

**1**answer

333 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 ...

**1**

vote

**2**answers

349 views

### Advanced spreadsheet auto-formatting

Is it possible to autoformat spreadsheet like this
IF A1 = C1 then A1:E1 cells background becomes light gray
I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet

**1**

vote

**1**answer

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

**3**

votes

**2**answers

4k views

### Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle ...

**1**

vote

**1**answer

458 views

### How can I open a subset of a large (35MB) .xlsx file?

I have Ubuntu 10.04 running on a Dell Optiplex with 4GB of memory and two 3.16GHz processors.
I received a 35MB spreadsheet. It opened in Gnumeric after 5 minutes with errors, and it hasn't opened ...

**0**

votes

**1**answer

61 views

### How to list people below the average in Excel/OpenOffice?

I have a spreadsheeet that shows a list of people and how many appointments have been booked for each of them in the past week.
It looks a bit like this:
I want to make a list underneath that ...

**0**

votes

**3**answers

4k views

### Export spreadsheet and import as table into mysql

What's the easiest way to export a table from either MS-Excel or OpenOffice calc and import it into a mysql database, where:
The table will be imported as a table in Mysql
The headers of the table ...

**0**

votes

**1**answer

1k views

### Remove last n chars from a column in a CSV?

Is there a way in microsoft excel or open office calc to remove the last n chars from a field.
Lets say all fields in one column are 31 chars long and I want to remove the last 6 chars.
Is there ...

**3**

votes

**2**answers

430 views

### Days in year between certain dates

In OpenOffice (or Excel), I need to calculate the exact number of days that a certain date range falls within a certain year. Eg. the date range 2009/1/1 to 2010/11/01 has 365 days in 2009 and 305 ...

**1**

vote

**4**answers

115 views

### What is the least-error-prone way to share CSV data?

One of my jobs is to upload product data to the company store. The data is initially pulled from the company database by my supervisor, and she puts it into an Excel spreadsheet and exports as CSV. I ...

**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

104 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

**3**answers

2k views

### Excel: How to sum values in a column based on multiple criteria

I have a list of criteria in row 20, like this:
A20: "My Company, Inc."
B20: "Client Expenses"
C20: "Travel"
The data is below, like this:
B40: "Client Expenses"
C40: "My Company, Inc."
D40: ...

**0**

votes

**4**answers

915 views

### Difference between CSV in Excel and CSV in OpenOffice.org

There are different implementations of Comma Separated Values; two of them are Microsoft Excel and OpenOffice.
Is there any difference between these CSV implementations? For example both can open ...

**1**

vote

**1**answer

2k views

### Excel or open office calc how to remove last white space? [closed]

Excel or open office calc how to remove last white space?

**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

**2**answers

942 views

### Prevent Excel from updating absolute references when target is moved

I would like to prevent OpenOffice Calc or Microsoft Excel from updating absolute references when their target cells are moved.
For example, if cell B2 is "=$A$1" and you cut A1 and paste it in A2, ...

**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 ...

**1**

vote

**2**answers

6k views

### embed pictures from web by url in excel spreadsheet (or oo calc)

I want to embed a picture from the web in an excel spreadsheet.
I want to embed the picture by url, in the same manner that pictures are embedded in an html page. I don't want to download the picture ...