Tagged Questions

0
votes
0answers
48 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
3answers
336 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 ...
0
votes
0answers
56 views

Apply Header Image to OpenOffice Spreadsheet

I am attempting to apply a background image to my Excel(Open Office Calc) spreadsheets in Open Office Calc. My Problem: The header image is cut off by the spreadsheet columns. How can I make the ...
0
votes
1answer
180 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?
0
votes
0answers
85 views

How to create a excel spreadsheet that measures bed cleaning turnover? [closed]

Currently working a healthcare facility that does use a bed tracking system. I want to submit a monthly report that will tell the time in minutes it takes to clean a patient room. (turnover a room ...
2
votes
2answers
137 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 ...
2
votes
2answers
249 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 ...
0
votes
0answers
283 views

How do I use conditional formatting in Open Office Calc to use an image for the background instead of a color?

Ok so we have a spreadsheet that we are trying to reproduce in Open Office Calc that was created in Microsoft Excel. The feature that we are trying to emulate is using an image instead of a background ...
0
votes
2answers
2k 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 ...
6
votes
6answers
8k 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
2answers
352 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. ...
0
votes
1answer
531 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
1answer
461 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
1answer
759 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
1answer
221 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
2answers
247 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
1answer
2k 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
2answers
2k 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
1answer
227 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
1answer
52 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
2answers
2k 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
1answer
720 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
2answers
251 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
4answers
105 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
2answers
1k 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
1answer
92 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
3answers
1k 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
4answers
771 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
1answer
1k 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
1answer
2k 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 ...
1
vote
2answers
628 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, ...
2
votes
3answers
3k 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
5answers
4k 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
2answers
4k 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 ...