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

learn more… | top users | synonyms

6
votes
1answer
9k views

How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...
0
votes
1answer
101 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 ...
1
vote
0answers
200 views

How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
1
vote
3answers
157 views

Collect Row # of Matching Elements in Excel

I'm trying to collect the row numbers that match a certain value in multiple columns of an excel worksheet. The behavior I'm looking for reflects the table below: |---|---------|---------|---------| ...
0
votes
1answer
4k views

Excel Spreadsheets: If A:A does not equal C:C, how do i list columns A, B and C somewhere else?

I have an issue with referencing an excel spreadsheet: I have data in columns A and C that's (mostly) equal, I want to isolate and create a separate list for the rows that are not equal. For ...
2
votes
3answers
6k views

In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture. Sorting ...
0
votes
1answer
221 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 ...
3
votes
2answers
7k views

Split Text into Columns Function in Excel

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number. QA32 Product One (Large) 23 AZ63 Product Two 96 PR65 Product Three 149 How do ...
1
vote
1answer
168 views

Is there another way to display a spreadsheet within OpenOffice Writer besides an OLE object?

I'm writing a paper for college in which I would like to either display or link to a large spreadsheet. I'm using OpenOffice Writer and Calc. I've tried importing the spreadsheet as an OLE object, ...
0
votes
1answer
76 views

Excel: How to efficiently check whether a selected cell is referenced in any other cell

I want to check whether a selected cell ( say A1) is referenced in any other cell in the sheet I’m working in? Is there a similar tool that shows you arrows from the selected formula to referenced ...
0
votes
1answer
842 views

Copy distinct values in excel from one range to another

Suppose I have a table like this: A B food 12 cars 30 cars 40 food 10 food 14 living 100 fun 30 food 40 I'd like to copy all distinct values from column A to some other ...
0
votes
1answer
89 views

Is there a way to preformat a message in lotus notes?

I frequently send e-mails regarding inventory with a very particular format. I'm trying stationary and am wondering is there something that checks if the right number of characters are entered for the ...
1
vote
1answer
209 views

excel: convert transaction list to balance

If I have transcation data like this: (date,spend,balance) 2013-01-01, ,$60 2013-01-03,-$10,$50 2013-01-04,-$10,$40 2013-01-04,-$10,$30 2013-01-06,+$20,$50 How can I convert it to a list of ...
0
votes
2answers
146 views

Excel/Spreadsheet - formula for last value in a row?

I'm trying to forecast increasing vendor costs in a spreadsheet. My sheet looks something like this col | A | B | C | D | E | F vendor | 2013-09 | ...
0
votes
2answers
102 views

Excel Equations for Spreadsheet

I have a excel spreadsheet, and while I like to think of myself as quite the excel wizard, this one has me stumped, it's probably something ridiculous but I really can't get it too work. I have a ...
1
vote
1answer
146 views

How does Open Office Calc make this conversion?

In a column, I have dates of the format 2013-05-27. When I selected "Default Formatting", the dates got changed to five digits, for example, 41422. Where does this number come from? It's not even ...
0
votes
0answers
289 views

enteries in column are automatically changed to upper case letters

I am very new with Open Office and spread sheets in general. In a column whenever I add a new entry it changes all the letters to upper case. Is there a way to disable this? If not, many entries ...
0
votes
2answers
1k views

Merging multiple versions of same excel spreadsheet

So here's the situation: I have multiple versions of the same spreadsheet-- each one has the exact same row and column labels. The difference between any two given spreadsheets is that data in one ...
-1
votes
0answers
1k views

Google Spreadsheets Error: All series on a given axis must be of the same data type [closed]

I am trying to use embed interactive column charts from Google Spreadsheets for a website. However, when I enter the HTML code into the website I get the following error: "All series on a given axis ...
0
votes
1answer
383 views

Open Office Calc formating table

Instead of data, I would like the first row to be the names of the columns. How can I do this? One of the reasons I want this is so that if the rows are rearranged the names wont be jumbled up. I ...
5
votes
2answers
687 views

Is it possible to access and edit Google documents directly from Libreoffice?

I'd like to work with a Google spreadsheet directly inside Libreoffice, without having to export and import it, in order to automatically reflect the changes on the online file. Basically, I'm looking ...
0
votes
4answers
22k views

=if() function is not working like it should be

I am trying to use an IF function in a formula in MS Excel 2013, but every time the IF function ignores the condition I wrote and goes to the true value. I checked my IF statement over and over, and I ...
3
votes
3answers
35k views

Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data. Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...
0
votes
2answers
64 views

Using conditions to write to new row on new sheet to return value

I would like to be able to use a data-entry page or form to keep track of monthly fulfillment of orders. We have some number of SKUs, lets say 10, and various number of orders against each SKU. ...
3
votes
2answers
25k views

Dynamically filtering data before importing from SQL Server in Excel

I'm trying to figure out whether there is a way to filter the data from an SQL Server connection before it is imported into Excel. The initial import doesn't matter (when the connection is set up), ...
0
votes
1answer
46 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 ...
1
vote
1answer
5k views

How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once? I have been using what I ...
2
votes
0answers
153 views

Unable to enter data into cells in sc

I'm trying to use sc, the classic Unix spreadsheet calculator, mainly because I want to be able to look at some csv files nicely formatted in my terminal. I installed sc 7.16 but am seeing some ...
0
votes
1answer
304 views

Retaining cell formatting in a formula

I'm looking for a way to pass along text formatting when combining cells in a formula. The image attached shows a similar instance of the result I would like. If I combine A1 and A2 and A3 with the ...
0
votes
1answer
226 views

Vlookup not working as expected for soccer data

i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, ...
2
votes
2answers
131 views

Why is this simple Excel calculation not netting Zero?

It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end. Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx
0
votes
2answers
657 views

How do I add ROUND function to formula

I have following formula, and need it to round to two decimal places =IF(ISBLANK(N2),"",10/(N-1)) How do I do this?
1
vote
2answers
23k views

How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1 Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value. I don't want the C-column. ...
0
votes
1answer
296 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
25 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
444 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
4k 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
18k 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
10k 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
648 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
345 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
183 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 ...
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
116 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
2k 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 ...