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

learn more… | top users | synonyms

0
votes
2answers
851 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
3k 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
1k 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
1k 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
928 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 ...
2
votes
2answers
2k 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 ...
1
vote
1answer
822 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
585 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
4k 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
567 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
21k 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
705 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
90 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
4answers
20k 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
2k 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
9k 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
868 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 ...
2
votes
8answers
24k 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
703 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. ...
1
vote
1answer
320 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
378 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 ...
0
votes
1answer
688 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
1answer
60 views

Auto updating similar formulas

I'm trying to see if there's a way to get my Excel sheet to auto update with new formulas as I enter it. For example if D4 down to D27 was the same formula(=SUM(B4/25)) and I wanted to change it too ...
2
votes
2answers
2k 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
139 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?
6
votes
3answers
9k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
0
votes
2answers
556 views

Unique string values in range

I have some spreadsheets where there are large number of cells that have essentially been used for free text. There is a finite set of values for this free text and most, if not all repeat. eg. ...
0
votes
1answer
106 views

Referncing a cell, based on a cell's value in Spreadsheet

I understand how referencing a cell works, example being =Sheet1.A1 However, if I'm in Sheet2 and cell B1 contains Sheet1, and cell C1 contains A1. Is there any way to write the reference such ...
0
votes
1answer
2k views

Syncing two sheets, while being able to hide different data

I'm pretty new to excel- so please bear with me. I have created a spreadsheet to organize gear by serial numbers and by who has it. This list is getting updated multiple times daily as gear shuffles ...
0
votes
1answer
215 views

Gnumeric doesn't recognize numbers

I am trying to do some basic calculations (mostly finding the median of a range of values) using gnumeric. Unfortunately, this is giving me a hard time. Consider the following data: 0.04 0.04 0 0 ...
0
votes
1answer
403 views

Dynamic time in Excel spreadsheet column

I am trying to automate time in Excel. I have two columns that show time advancing in minutes and seconds. I want to be able to automate so that if the time changes in one row, it will automatically ...
1
vote
2answers
287 views

Is there anyway to display spreadsheet as a tiny window without toolbar?

My screens are full of stuffs, still need to add a small spreadsheet, maybe one inch high and less than one inch wide. Is there any solution for this? I don't do complicated calculations, just some ...
3
votes
2answers
824 views

Excel-like software for XML data?

Is there any Excel-like software for XML data? Something not restricted to spreadsheet format, but allowing: data with hierarchy more strict typing tagging multiple views to the data, unlike ...
0
votes
1answer
111 views

Retrieve info from a column with some empty cells from ODF Spreadsheet

I have this ODF Spreadsheet on LibreOffice open, at it has one column with a lot of data that I wish to retrieve, however, I also have, a lot of white cells. Something like this: emailA emailB ...
1
vote
1answer
1k views

Filtering by currency in Excel

I have this situation in Microsoft Excel: Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...
1
vote
4answers
936 views

How to view a delimiter-separated-value file in Emacs with highlighting helpful for reading?

How to view a delimiter-separated-value file in Emacs with highlighting helpful for reading? Ideally the delimiter character/string should be customizable. If Emacs can't do, any other tool ...
2
votes
3answers
17k views

Excel: Multiple =IF calculations within one cell

I need to do some text comparisons in excel, about 20-50 of them. I would like the result of these if statements to be within a single cell. Here is how I WISHED it would work: ...
3
votes
2answers
184 views

Alternatives to Excel

Is there some WYSIWYG utility that allows one to create simple spreadsheets? Something like standalone Google Docs. Excel is definetely an overkill for someone who doesn't want to create exotic ...
1
vote
1answer
2k views

How to use multiple criteria with COUNTIF?

INPUT: This is the format of the OUTPUT desired: OUTPUT for example INPUT: The problem is, I don't know how to use COUNTIF with several criteria. The "dream function" [that doesn't work] ...
3
votes
3answers
17k views

How can I enable the creation and editing of hyperlinks in shared Excel worksheets?

I have hyperlinks in some Excel 2010 (both .xlsx and .xls formats) worksheets. If I enable sharing of the workbook I can no longer edit or create hyperlinks - the options no longer exist on the menu ...
2
votes
0answers
331 views

Multilingual spreadsheet

I need to make a spreadsheet template that needs to be support both English and Spanish. I know Office 2010 has an option to translate content into desired language but its a manual process. Is there ...
17
votes
1answer
14k views

What is in LibreOffice Calc the equivalent to Excel's “Paste Special > Value”?

In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value". For instance A1 = "I",B1 = "ride", C1 ...
3
votes
2answers
12k views

How to break a spreadsheet containing CSV into multiple columns?

I'm working on preparing data for export, and the client has put all of the data into a spreadsheet. Each field of data should be its own column. The problem is that the client erroneously put ...
5
votes
2answers
2k views

How to sum cells depending on the content of a neighbor cell

I have an Excel document with the following columns; Date | Reference | Amount 23/01/11 | 111111111 | £20.00 25/09/11 | 222222222 | £30.00 11/11/11 | 111111111 | ...
1
vote
2answers
1k views

How to filter a spreadsheet on multiple filter values

I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these ...
4
votes
1answer
8k views

Can you use infinity in a cell reference in LibreOffice (or any other spreadsheat)? [duplicate]

Possible Duplicate: Can you reference an entire column in OpenOffice Calc (like A:A in Excel)? I'm using a Sum of a column, and it keeps growing, so I have to update the reference to which ...
-1
votes
2answers
54 views

copy worksheet to several workbook

I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook. I have ...
2
votes
1answer
4k views

OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1. =IF(A1>B1;"A";"B") will display A in ...
1
vote
2answers
1k views

Multicolumn spreadsheet: extract unique values in Column 1, given same values in Columns 3 and 4?

Here's a problem: given a (large) spreadsheet with userIDs in Column 1, a time/date pair in Column 2, a course code in Column 3, and a course title in Column 4: I want to produce a report showing how ...
2
votes
2answers
9k views

How can I print Excel spreadsheet files automatically?

My company has computer-generated reports that come out as Excel spreadsheets which are emailed to the Billing Department. They would like these reports to be automatically printed as they are ...