Tagged Questions

-1
votes
0answers
19 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 ...
1
vote
2answers
77 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
15 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
58 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
48 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
1answer
69 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
111 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
59 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
337 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
219 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
117 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 ...
1
vote
1answer
123 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
vote
0answers
64 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
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 ...
0
votes
1answer
64 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
246 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 ...
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
309 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
200 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
143 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
653 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 ...
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 ...
0
votes
1answer
44 views

Excel 2007 Ability to have other access the sheet with same format

I created an excel spread sheet that fits exactly on my screen and saved it in a shared drive so my team mates can access it. It has some macros and other sheets on the right side of the main ...
0
votes
2answers
271 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
453 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 ...
1
vote
3answers
429 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
521 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
463 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 ...
1
vote
1answer
2k views

Compare cells in two different spreadsheets and extract data from one an place it in the other if match found

I need to find a way to compare two spreadsheets and if there is a match on specific cells, pull data from one sheet to another. Say the two spreadsheets contain a value that identifies a piece of ...
1
vote
1answer
389 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
195 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 ...
2
votes
4answers
1k 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
275 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 ...
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 ...
0
votes
1answer
359 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
70 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 ...
2
votes
3answers
1k views

To much text for an Excel cell

What do I do when I have more text in an Excel cell than I want to (was "can") show in the allocated space? (Something making it "scrollable")
2
votes
3answers
4k 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?
1
vote
3answers
351 views

In MS Excel 2007, replace numeric value with same value plus function

For example, if Column A contains: 123 456 789 I want to replace values with: =(123/60) =(456/60) =(789/60) I know I could create another column to do something similar, but would rather know ...
0
votes
3answers
5k 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
357 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
422 views

Searchable tags in Excel?

I'm building a database of spiders, organizing them by name, country, and region within that country. The problem is that many of the spiders exist in multiple countries and regions within that ...
0
votes
2answers
478 views

Turn Excel spreadsheet into a formula

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...
0
votes
1answer
100 views

Excel: Adding and Searching Replacing in Bulk from Many Sheets

I've got a 650 sheet Excel document. I need to do some bulk additions to each of the sheets. Are there ways to say add a bit of text to a specific row/column pair (A4), bulk search and replace, etc?

1 2 3