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

learn more… | top users | synonyms

0
votes
2answers
40 views

keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence? The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...
0
votes
1answer
438 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
0answers
184 views

Is it possible to recover cells with hidden content wiped out upon opening with LibreOffice

Some of my .xls Microsoft Excel files contain cells with hidden information formatted using custom code ;;; to deter casual viewing. I made the switch to LibreOffice a year ago and converted all the ...
-1
votes
1answer
541 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
3
votes
1answer
5k views

Disable prompts in Microsoft Excel when saving a CSV file [duplicate]

I have to download multiple csv reports from the Internet throughout the day, make a tiny change to them, and save the change. Excel adds 3 completely unnecessary dialogs to what should really be a 5-...
1
vote
1answer
29 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
0
votes
1answer
77 views

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}?

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}? My intention is to calculate a vehicle's running average (annual)...
0
votes
1answer
84 views

IF (AND () function does not work as it should be

When I write the following simplified command in a cell, =IF(AND(C6="A";D6="30");D2;0) it does not appear the value of the cell D2 but zero, depsite the fact that the 2 assuptions of IF(AND) are ...
0
votes
2answers
172 views

Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that: =SUM(A1:INDEX(...)) Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function. The ...
0
votes
1answer
179 views

How can I do this in spreadsheets?

Let's say you have a table like this: | A | B | C | ------------------------------------- 1| sex | age | answer q1 | ------------------------------------- 2| ...
3
votes
1answer
69 views

How to fold a horizontal series into a vertical one

I have a set of data that is contained in a .csv spreadsheet arranged like this: name | 2006 | 2007 | 2008 | 2009 -------------------------------- foo | 28 | 48 | 395 | 3829 bar | 83 | 475 |...
0
votes
1answer
18 views

Calculating non-duplicates

I have below data in three columns. A B C 07002 72J217.4 72J217 07002 72J229.5 72J129 07002 72J229.6 72J129 07002 72J229.7 72J129 07006 61E001.5 61E001 ...
2
votes
0answers
145 views

How do I import multiple csv files in gnumeric?

I am trying to import multiple csv files into gnumeric so that each one is a separate sheet in the workbook. This is relatively simple in Excel but I cannot work out how to do it in gnumeric.
2
votes
3answers
3k views

Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.
0
votes
1answer
145 views

Excel: Copy identical data sorted by date-time to future dates

I apologize in advance if this has been answered before but I couldn't find anything using similar wording to my question. I have an Excel sheet of one year of data, with a value for every 15 minutes....
0
votes
1answer
380 views

Using SQL to join spreadsheets in excel

Based on the explanation here: How do I join two worksheets in Excel as I would in SQL? I tried to join two excel sheets from different files into the same sheet. However, I keep getting this error ...
0
votes
0answers
132 views

Don't want to include blank rows in Excel table

I have a table in excel which contains the following columns: Brands: Cell B Open Closed: Cell C Case Number: Cell E Case Summary: Cell G At the moment these are the following formulas I am using ...
-1
votes
1answer
409 views

How to get data from separate worksheets displayed in a single separate worksheet

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as: 1st 2nd Cases Now in the 1st and 2nd Worksheets, there is a table at the bottom ...
6
votes
1answer
10k 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
104 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
225 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
160 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
5k 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 example,...
3
votes
3answers
7k 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
229 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
176 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
78 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
877 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
91 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
224 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
153 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
105 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
317 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
2k 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
409 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
775 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
26k 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
40k 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
28k 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
47 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
160 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
312 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
232 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
137 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
685 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?