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

learn more… | top users | synonyms

1
vote
1answer
4k views

How do I count the times each number appears in columns of numbers?

I am sure this must be easy, but I am inexperienced. About the best way to think of my problem is to think of it as trying to sort and then count lottery numbers. To stay simple, let's do a Pick 3 ...
2
votes
1answer
1k views

Quick/Small Spreadsheet Software

There is some minimalist spreadsheet (excel-like) software? I imagine something like NotePad (small and quick) just to write some tabular data quickly. No toolbars, no font formatting, not "a lot" of ...
1
vote
2answers
6k views

Big Excel File Freezing/Running Slowly

My co-worker has a very large Excel file (over 7 MB) that suffers from the problems of (A) running slowly (B) taking forever to open/save/close and (C) freezing the computer, requiring a restart. I ...
1
vote
3answers
3k views

Combine 3 columns into one in Excel 2011 for Mac

I am trying to manipulate a bunch of string elements I have put in columns in Excel. I then filled the preceding column with data, and then the following column as well. Now I want to delete the ...
3
votes
1answer
987 views

Open Source Spreadsheet with Sensitivity Analysis

Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...
4
votes
4answers
790 views

Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...
4
votes
1answer
774 views

Custom CSV (.csv) filter for OpenOffice.org or LibreOffice?

Is it possible to create a some kind of 'custom CSV filter' for OpenOffice.org or LibreOffice spreadsheet program. What I need is to have the program to use predefined CSV settings for loading and ...
1
vote
6answers
16k views

In Excel, copying cell *values* (not formulas)… yet keeping the cell formats

I have an Excel spreadsheet with multiple worksheet tabs for collecting data... and a final tab with a lot of formulas, to summarize the other tabs for a given date range. The goal is for the ...
2
votes
1answer
2k views

In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?

My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s). However, I have a table where a very important column needs to be located ...
0
votes
3answers
3k views

How do I add content to Excel cells?

How can I add a T to the content of each cell in a certain column (that contains 10000 cells) without typing it manually?
1
vote
3answers
3k views

How can I compare columns in Excel?

If I have two columns of data with 10000 cells in each, and only three of the rows are not identical in both columns, how can I locate the different ones (without comparing each pair of cells)?
2
votes
2answers
5k views

Excel - Dynamic row reference based on the row I paste a formula into?

I have a simple, oft-used formula that I paste as plain text into spreadsheets I receive. It looks something like this: =IF(D8="FOO", "BAR", "BAZ") It looks in D8 for the word "FOO". If it finds it ...
2
votes
1answer
710 views

Two Spreadsheets, Merge Data?

I was wondering what is the best way to merge two spreadsheets which have the name id's e.g: One sheet has: apple 5 dog 3 cat 2 Second sheet: dog 4 apple 7 cat 3 I would ...
1
vote
2answers
58 views

Spreadsheet like software, where I can add files/documents to Cells

Does anyone know of a spreadsheet type software like excel for instance where I can add files/documents to cells. The reason being is I would like to add information in the adjacent columns regarding ...
1
vote
2answers
135 views

Versionable text spreadsheet format, ideally with GUI editor

I'm looking for a spreadsheet format that whose diffs won't look insane if I version it. Ideally this would have a decent editor to go with it (GUI preferably), and should be available ...
1
vote
1answer
103 views

How to visualize historical data

I have inherited a horrible spreadsheet with 4 years of data about when people participated in a given activity, by month. Each person would likely participate once every 18 months or so, but ...
2
votes
1answer
41 views

Rearrange cells in a column to a different column with same heading (Excel 2002)

I need the values in one column to be rearranged for each set into another column. This picture will help explain: Follow the red lines, that is where I want my data to go. This shouldn't be too ...
0
votes
1answer
75 views

How do I select a set of specific cells and then copy their entire row?

Okay this is hard to explain. I have a massive spreadsheet with sets of data in it. Each set has 'type' column (eg: system, memory, video card, etc). I need to be able to search within this column ...
0
votes
1answer
2k views

Microsoft Excel Exercises for learning purposes

I'm not sure if it belongs this here, but if you feel it doesn't just please remove it. I'm looking for a good source of exercises in excel that is good in teaching a friend of mine that wants to ...
0
votes
1answer
1k views

Autosort microsoft excel worksheet

Can I configure (possibly with scripts) Excel to automatically resort a table every time an entry is updated?
0
votes
1answer
372 views

How do I simplify this if statement to just add 1 every time (and possibly go with no limit)?

=if(C24 ='Ability Base'!$A$2;sum('Ability Base'!$B$2); if(C24 ='Ability Base'!$A$3;sum('Ability Base'!$B$3); if(C24 ='Ability Base'!$A$4;sum('Ability Base'!$B$4); if(C24 ='Ability ...
3
votes
1answer
5k views

Excel: #NAME? appearing when linking/referencing other spreadsheets/workbook cells

I'm updating a spreadsheet that refers to data in another worksheet =NameofWorksheet for some reason I get #NAME? in some of the cells, even when the actual path is correct. The only way to overcome ...
2
votes
2answers
3k views

how to find lowest 2 or more values

I have rows of data and would like to sum all but the lowest 2 or 3 or 4 entries. What is the easiest way to do this? Does it involve a macro. I prefer open office spreadsheet, but could also use ...
2
votes
1answer
165 views

Conditional Formatting in Excel

I have a worksheet with a series of rows A1 - H1 etc and a drop down list which lives in H1, I would really like to change the cell styles of all the cells between A1 - H1 based on the value in H1. ...
1
vote
2answers
435 views

Not counting values if strikethrough in iWork- Numbers

I'm building a holiday expense planner and I'm trying to get the sum of a column in a table, however as I pay things off I want to be able to strikethrough the amount and then it minus it from the ...
2
votes
3answers
631 views

Excel data representation: show me all people who did not pass the exam

Background I have an excel spreadsheet with the results of a pass/no-pass exam. Students are allowed to take the exam as often as they want until they either pass, or give up trying. student ...
1
vote
1answer
139 views

Inserting scientific data into open office Calc

i have some scientific data that i want to insert into oo.org calc. when i do this, instead of displaying 0.35678 in a cell it displays 0.35 (only the first 2 digits after the decimal). but on ...
2
votes
3answers
222 views

Spreadsheet RDBMS

I'm looking for a software (or set of software) that will let me combine spreadsheet and database workflows. Data entry in spreadsheet to enable simple entry from clipboard, analysis based on joins, ...
13
votes
2answers
4k views

Excel Conditional Formatting Escaping a Question Mark

I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn ...
0
votes
1answer
1k views

Make cells automatically shrink to fit text in OpenOffice spreadsheet

Is there a way to get the widths of cells to automatically shrink themselves to fit the longest line in a column?
4
votes
5answers
14k views

Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...
1
vote
2answers
88 views

I hyperlinked a cell in excel 2003, formula issues?

I have a budget spreadsheet using excel 2003. I have My deposit, then all of my bills, the total, then a cell that has the difference(between the amount of deposit and the total of the bills). The ...
0
votes
1answer
292 views

MS Excel convert cell matrix (1x15 -> 3x5)

What is the formula for displaying cells in a 3 cell increment at a time, like A1, A4, A7, A10? Moreover, would it be possible to transform below a list (1x15) of data into a table(3x5). Thanks. ...
0
votes
3answers
922 views

Any examples of a complex, changeable system modelled with a spreadsheet?

When I asked this question using games as the example (hoping it would be more likely to have been done), it was closed as being off topic so let me ask it this way... Has anyone used a spreadsheet ...
1
vote
1answer
4k views

Excel cell references not updating when referenced cells are sorted

There are two tables, each with 75 entries. Each entry in the 2nd table calls an entry in the first table a parent. One of my 2nd table columns contains the "Parent Price", referencing the Price ...
0
votes
2answers
646 views

Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range? I know that min() would give you the lowest value but i need something to return the 100 lowest values. I just used 100 ...
1
vote
3answers
755 views

How can I detect if a range of cells contains only identical values or not in Excel?

I have a range of cells and I want a formula that says if all these contains the same values or not. Usually for two cells I would use if but I want a solution that would work for ranges. I don't ...
1
vote
1answer
304 views

Store Solver's metadata in OpenOffice's SpreadSheet

How can you save the metadata of OpenOffice 3.0's Solver? I do not want to add the conditions each time I open a file in OO's SpreadSheet. Excel by contrast saves the Solver's Limiting conditions.
3
votes
2answers
12k views

How do I test if a cell in OpenOffice Calc contains #N/A?

I'm using the VLOOKUP-function to lookup values in a column based upon a search query. But since it's not a given that a value always exists for my search parameter, sometimes VLOOKUP returns #N/A ...
0
votes
3answers
263 views

Apply a formula to particular cells in Microsoft Excel

Say I have the following sheet: | A | B | C | 1 | 5 | 1 | 5 | 2 | 8 | 8 | 99 | 3 | 77 | 3 | 3 | 4 | 2 | 7 | 4 | 5 | | | | 6 | Base:| 2 | | ...
3
votes
1answer
783 views

Read data from separate CSV file in OpenOffice Calc

I have a very large CSV file (many thousands of rows) that I want to work with. Due to the size of the file, I don't want to import it into openoffice. Rather, I want to create a spreadsheet that ...
1
vote
4answers
972 views

How to prevent truncation of 0 in Excel sheet?

I am copying tabular data from a website: [http://www.infodriveindia.com/TradeResources/STDCode.aspx?STDCode=&City=&State=andaman][1] But it pastes the numeric value by truncating the ...
2
votes
1answer
1k views

Looking for Open Source, locally-hosted, web-based personal finance or spreadsheet software [closed]

I'm looking for a web-based personal finance management solution, but one that I can host locally, so not something like Mint.com or the like. I'd even be willing to look at a web-hosted spreadsheet ...
2
votes
3answers
23k views

How do I open or convert a Microsoft Works .XLR spreadsheet to an Excel compatible format?

I have been sent a Microsoft Works XLR spreadsheet and Excel will not open it. Is there any way of opening an XLR file directly in Excel? Can anyone recommend a (free) tool for converting to an ...
3
votes
2answers
2k views

Count cells based on text content (Gnumeric)

I have a spreadsheet with columns that are basically booleans, they contain either "Yes" or "No", I want a total count of all the cells in a range that are "Yes". I imagine this would be done ...
0
votes
1answer
244 views

Is there a freely available source for VisiCalc documentation?

I am looking for a freely available online document with instructions on how to use VisiCalc
1
vote
3answers
766 views

How do I read old star office 5.x spreadcheet documents today? (*.sdc)

I found a couple of old spreadsheet documents that I created a couple of years ago that I would like to read again. I think I used the old Star Office 5.2 to create those Spreadsheets. They all ...
3
votes
3answers
4k views

Excel annoyance: How to maintain formulas in a ledger?

A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...
5
votes
2answers
6k views

In Open Office, make 1st row of data “scroll lock” so it is always visible as you scroll down?

In Open Office, how do you make the first row of data "scroll lock" so it is always visible as you scroll down?
6
votes
4answers
20k views

Select each nth row in Excel

I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new ...