Questions about spreadsheet software in general and interoperability between spreadsheet programs.
0
votes
0answers
16 views
multiply cells in a column with results dragged horizontally in another sheet
I want to multiply a cell in 1 column by another cell lower down in the same column but have the answer come out on another sheet and I want to drag this formula horizontally so that the answers to ...
0
votes
0answers
4 views
Google Docs spreadsheets: how to specify entire sheet as range? [migrated]
The best workaround I have found so far is:
worksheet_name!$A$1:$YY
but ideally I would like to be able to simply write, e.g.:
worksheet_name!
So, does anyone know: is there a syntax for ...
0
votes
0answers
10 views
Automatically skip cells with prefilled data when using a barcode reader
I have a spreadsheet with data permenently entered into specific cells. I am using a barcode reader to enter data into the empty cells. How do I get the barcode reader to automtically skip the ...
0
votes
2answers
26 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. ...
1
vote
2answers
76 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), ...
-2
votes
0answers
55 views
Summarising Excel data
I have two rows of data, one in column A containing amounts and column B containing a text description obtained from a drop down list using the Data Validation -List Function, ie Text1, Text2, Text 3 ...
0
votes
1answer
14 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 ...
0
votes
1answer
54 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 ...
-1
votes
1answer
44 views
Excel, comparing data in two columns
I have two columns with various amounts. I know that amounts in column A add up to match numbers in shorter column- B. Is there any way Excel could do this calculation for me and show me which numbers ...
0
votes
0answers
6 views
Conditional formatting color gradient in Google Spreadsheet [migrated]
A few friends and myself have created a Google Spreadsheet document where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a ...
1
vote
0answers
23 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
32 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
67 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
74 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
43 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
1answer
106 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
58 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
23 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 ...
0
votes
1answer
46 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
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
1answer
214 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 ...
1
vote
1answer
114 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
33 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 ...
1
vote
1answer
119 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
67 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 ...
2
votes
1answer
70 views
1
vote
0answers
63 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
21 views
Software or script to check frequency of certain terms in a spreadsheet against an HTML document
So I have a list of names in a column on a spreadsheet. I also have an HTML file containing text with these names appearing in it sporadically.
How can I check the frequency of appearance of the ...
0
votes
0answers
67 views
Place multiple columns onto one line
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
109 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
108 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
0answers
36 views
OpenOffice Calc Searching a table containing drawn lotto numbers
I have a table with drawn lotto numbers (1-49), prizes etc.
Date n1 n2 n3 n4 n5 n6 city prize Retailer
...
0
votes
1answer
63 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 ...
1
vote
1answer
169 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
242 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 ...
2
votes
1answer
132 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
64 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 ...
1
vote
1answer
240 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
74 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
305 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
2answers
192 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
142 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 ...
0
votes
2answers
650 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
70 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
0answers
55 views
How do I paste a couple of paragraphs in a block of merged cells?
I am using LibreOffice Calc. My goal is to put two boxes of text on specific places on every page. The text for each textbox is to be two short paragraphs.
However, this happens:
How do I fit the ...
0
votes
1answer
152 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
23 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
42 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
1answer
165 views
Interactive console based CSV editor
Although spreadsheet applications for editing CSV files on the console used to be one of the earliest killer applications for personal computers, only few of them and even less documentation about ...
0
votes
1answer
262 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 ...
