Questions about spreadsheet software in general and interoperability between spreadsheet programs.

learn more… | top users | synonyms

0
votes
1answer
313 views

Split a file with macro in excel

I know there are 2 similar questions. I have a very large Excel file separated in sections by 3 blanks lines. I was wondering if there was a way to split the spreadsheet into other every time there is ...
0
votes
1answer
27 views

Excel 2007 removing entries which have data in two columns which may not match exactly

I have an Excel spreadsheet with over 10,000 entries. This spreadsheet contains two columns of dates, and I need to select only those entries where the dates do NOT match in the two columns. The ...
1
vote
1answer
473 views

Paste from text editor to spreadsheet keeping CR in one cell

I have tried this in Notepad and Notepad++, but can't get it to work. What I want to do is close to the opposite of this post: How to copy multi-line text from Excel without quotes? I want to paste ...
0
votes
3answers
5k 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
votes
2answers
20k views

How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following: Cell A1 ABCD 1234 I would like to split this to be CELL A1: ABCD CELL B1: A CELL C1: B CELL D1: C CELL E1: D I tried using the ...
3
votes
1answer
12k views

Excel 2007 - changes on one sheet affecting other (without cell references)

Some strange behaviour I've encountered twice in Excel is that changes made to one sheet would be duplicated on another sheet in the same workbook. The first time, I typed values into a few cells on ...
0
votes
1answer
669 views

Spreadsheet fill down for a formula

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell). When I used ...
2
votes
1answer
7k views

Lookup the nearest value in a column?

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D. So, if A is 160 I want to ...
-1
votes
1answer
358 views

Ways to delete or isolate entire rows in a huge data collection

I'm working with a huge datafile (about 400,000 rows and 8 columns) in Libre Office. I need to get rid of certain rows so I selected all cells with the desired value (with Find & Replace) and ...
3
votes
1answer
2k views

Formula to add X months to a date?

How can I add X months to an existing date in iWork Numbers?
2
votes
0answers
188 views

Merging CSVs with some common columns (though columns are in different places)?

I'm looking to merge CSVs that have common columns, but the columns aren't always necessarily in the same place. The CSVs do have a header row. I just want to try to get the data appended to the ...
0
votes
0answers
86 views

Place multiple columns onto one line [duplicate]

Let's say I have a table in a spreadsheet that works like a calendar: Jan Fev Mar ... 1 5 7 1 2 3 4 9 3 8 3 1 4 2 9 0 5 2 1 2 ... And so on... I need to make a chart ...
0
votes
2answers
2k views

Merging 2 spreadsheets on Excel 2010

I was wondering if it was possible to merge data from 2 different spreadsheets together. I'm looking at at world development indicators, so I have 1 spreadsheet which has human development index (HDI)...
1
vote
1answer
1k views

Binary spreadsheets with bitwise operators [closed]

I am looking for a way to easily construct binary spreadsheets using bitwise operators such as SHIFT, AND, OR and XOR. I've looked at Gnumeric but can't find any function that'd allow you to set the ...
0
votes
1answer
119 views

Can Excel have some text above 1st page (and only 1st) of the table? [duplicate]

Possible Duplicate: How to get the Excel header on each print page to be the first row of the table Can we print Excel report like this ? ------------------- Some description Some ...
3
votes
1answer
2k views

Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...
1
vote
1answer
3k views

Excel Offset Function Returns #VALUE! Error When Combined with Row Function

Why does following Excel function return an error? The error occurs only when the reference that is returned is larger than 1 cell. In the formula, the returned reference is 1 row high and 2 rows high....
3
votes
1answer
1k views

Sum the first N elements in a spreadsheet column

I'm trying to sum the first N elements in a column, where N is a value in another cell. I've come up with this: =sum(B1:indirect(concatenate("B", A10))) Where A10 contains N. But I don't want to ...
2
votes
2answers
195 views

How do I reorganize cells in a spreadsheet?

I want to take a spreadsheet with almost a thousand rows similar in format to this, Sheet1 Q A1 Bool A2 Bool A3 Bool Q1 Q1A1 1 Q1A2 0 Q1A3 0 Q2 Q2A1 0 Q2A2 1 ...
2
votes
1answer
11k views

Is there a way to delete everything except what is selected in Excel?

I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab. Is there a sneaky way to select that ...
0
votes
0answers
260 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 ...
6
votes
2answers
7k views

How to view large spreadsheet in Windows

What would you recommend to a non-technical Windows user for viewing (no need to edit) large spreadsheets (200MB - 5GB). Format is CSV and I am told Excel chokes.
3
votes
3answers
6k views

Deleting multiple selected rows in Excel

How can I delete multiple rows in Excel? If I mark everything and try to delete, it just deletes a single row. Even if I right-click and select delete, it still deletes one row at a time. I need to ...
4
votes
2answers
2k views

Automatically filling colors as per the values in the cell in spreadsheet

Question : Is it possible to fill each cell in a spreadsheet with a color whose grayscale value is same as cell value? Explanation : Consider I have a spreadsheet as follows : All values lie ...
1
vote
2answers
2k views

Create shortcut key to insert new row using Excel 2010

Tried to create a macro in Excel to insert a row: Sub InsertRow() ' ' InsertRow Macro ' ' Keyboard Shortcut: Ctrl+Shift+I ' Selection.EntireRow.Insert End Sub However, this does not insert a ...
2
votes
2answers
86 views

What is { } used for in Microsoft Excel 2010?

I came across this function in one of my worksheets and wanted to know what the curly braces are used for. Here is an example. {=array(sheet1!a1,sheet2!a3,sheet2!a5,sheet2!a6)}
0
votes
1answer
551 views

Rows disappear when removing duplicates libreoffice

I pasted a lot of data in Column A. Then I removed duplicates: Data > Filter > Standard Filter > More Options > No Duplication Now I'm stuck in row 2632, i.e. I can´t scroll up to rows ...
0
votes
1answer
36 views

Averaging unique columns in Excel

I'm probably missing something very obvious, but I have the following data: ACCT 4 ACCT 2 CS 20 CS 10 ENG 20 ENG 10 ENG 30 What I want is to get the average of each department. So it should ...
1
vote
0answers
78 views

Conditionally generate Excel sheet

I currently have an excel sheet that is used to record daily a bunch of parameters. Currently, I have a folder for each year, containing a folder for each month, inside of which I have an excel file ...
2
votes
2answers
929 views

Numbers imported into Ubuntu Libreoffice calc from text or csv files are coming in as text

I working in Libreoffice 3.6.0.2 in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and ...
0
votes
2answers
1k views

Make comma separated list from column with blanks, fed by checkboxes

I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes ...
0
votes
2answers
4k views

Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula. problem is the rota is written using a number of different notations for different shifts some are 2-...
1
vote
3answers
2k views

What Excel formats are (most) compatible with LibreOffice and Google Docs? [closed]

I use Excel (and occasionally Numbers), but I want to be able to share with users of Google Docs and LibreOffice (and I may want to switch in the future). What's the most compatible format to save my ...
3
votes
3answers
2k views

How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...
5
votes
5answers
1k views

Does any spreadsheet allow you to create your own functions?

I've always been frustrated when using spreadsheets by the inability to define my own functions when there is no existing function to (cleanly) do what I want. Does any spreadsheet offer this ability?...
2
votes
2answers
3k views

OpenOffice Calc - Highlighting the higest value in a column

So I've got this spreadsheet open using OpenOffice Calc (ver 3.3.0) and its set up a little like this: A B C D 1.name quantity price total 2.foo 10 ...
0
votes
1answer
1k views

Referencing a single row to multiple rows in Excel

I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row ...
3
votes
1answer
2k views

ADDRESS function nested in SUM returns Err: 502

When working on one problem, I came upon something that confuses me and was hoping for some clarification. I am trying to use the SUM() function in a very round about way. The formula =SUM(ADDRESS(...
2
votes
4answers
5k views

How to calculate how much I've worked per day?

I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...
2
votes
1answer
743 views

Spreadsheet application that can handle big data OS X

I've been working with Excel for quite a while for some statistical analysis that I do regularly. The size of the data that I'm working with has gotten much larger as of late, however. The layout of ...
1
vote
2answers
48k 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 ...
0
votes
1answer
918 views

Reference whole sheet in another sheet - reference dynamically NOT copy + paste

I have multiple sheets relating to each room in a house, I also have a master sheet showing the overview of the whole house, what id like to do is pull all the data from the room sheets into the ...
1
vote
0answers
93 views

Creating a script to make a CSV dump usable

So, let's say I have a raw CSV dump. And I want to make a spreadsheet/script to take said CSV dump, take a certain username, and the calls they made, and pipe those calls to a mathematical formula, to ...
4
votes
4answers
37k views

Too much text for an Excel cell — how to make the cell scrollable

What do I do when I have more text in an Excel cell than I want to show in the allocated space? Something making it "scrollable" would be preferred.
2
votes
1answer
4k views

Retrieve content of different cells based on input

I have a cell X where the user can enter a number. I then need to take that number N and get the value of another cell Y based on that. The scenario is this: The user can enter a product number and ...
2
votes
3answers
11k views

How to refer to the last cell in a row in Excel?

Which function can I use to get the value of the last not empy cell in a row in Excel 2010?
5
votes
10answers
46k views

Can't insert cells in Excel 2010 - “operation not allowed” error message

I was working on a spreadsheet in Excel 2010, and all of a sudden when I attempted to insert a new row of cells, I saw that the insert and delete options were grayed out. I attempted to copy a ...
0
votes
2answers
808 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. e....
1
vote
1answer
488 views

Alternative, more efficient scraping method for a noncoder, than Google doc's importxml and xpath?

I've searched throughout the net for a simple solution, but it seems everyone has their own unique method (coding language) of achieving this. I'm only just beginning to learn Linux, and my coding ...
1
vote
1answer
476 views

Unlink chart from data series in OpenOffice Calc

Is it possible to create a chart on a data series in Calc, and then unlink it so that if I sort that same data a different way, my chart wont change? i am trying to show some trends based on several ...