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

learn more… | top users | synonyms

0
votes
0answers
6 views

Using Query Function in Google Spreadsheet where {data, query, [headers]} is determined in another cell

For example in cell T2 contains the following text generated with the =CONCATENATE function DW004!$A$11:$H, "select H where (C contains """&$E2&""")", 0 This was created with the format ...
0
votes
2answers
34 views

Pulling out data from a spreadsheet

I need to add information of clients who have visited the office, which department, who they were referred by, who they were referred to etc. I have linked a screenshot of what I have currently made ...
13
votes
5answers
43k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
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 ...
4
votes
1answer
771 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 ...
0
votes
0answers
12 views

What's best way to print a 22000-row spreadsheet onto least paper?

I have a Google spreadsheet containing the titles of around 22000 songs, alphabetically organised. I want to print it to have as a song list for a party. If I do a regular "Print...", selecting A4 ...
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 ...
0
votes
1answer
36 views

How to prevent all-filtered-out row label values from appearing in a pivot table?

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns: Name: A string. CountMe valued either 0 or 1. Year an integral number. Tokens the data we're ...
2
votes
4answers
10k 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.
0
votes
1answer
29 views

Multiple users editing the same Spreadsheet, is it possible? [closed]

I have used a piece of software called Prezi for multiple users to edit a presentation at the same time, I find this a very helpful tool as if lots of data is distributed across a number of minds it ...
0
votes
0answers
14 views

Google spreadsheet to open another Google spreadsheet populated [migrated]

What I would like is to click on a name in a cell in one spreadsheet and it will populate a new spreadsheet giving the title in ascending order such as A001 next time you click it it will populate a ...
2
votes
1answer
2k 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 ...
0
votes
1answer
42 views

How to get plain text, colon separated file into spreadsheet with data separated into columns

I received a digest of registrations for events. The fields are separated by colons and in plain text. Can I copy and paste this info into libreoffice spead and get it displayed in columns?
1
vote
6answers
14k 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 ...
14
votes
1answer
11k 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 ...
2
votes
6answers
5k views

Where can I find some dummy data to teach my friend how to use a spreadsheet? [closed]

In particular, tabular data that can be graphed. What's your favorite source of interesting data?
0
votes
1answer
11 views

Remove symbols while appending in excel

I'm having some parsing issues here. I have a column of Twitter handles, with '@' in front of them, and I'm trying to auto-append them in a URL on the next column. Example: | @Bob | ...
-4
votes
2answers
83 views

Custom Data Validation in Excel 2010 [closed]

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use? ...
1
vote
1answer
44 views

Automatically sorting a range of cells after one cell within the range has been modified?

I have a number of named vertical ranges on a spreadsheet, containing lists of names. New names are usually manually added at the very end of each list. Obviously, this means that unless manually ...
10
votes
1answer
14k views

How to sort an Excel spreadsheet columns by row contents?

How do I sort the columns of an Excel spreadsheet? The Sort command from the Data menu only allows me to sort rows by the value of a column. I want to sort the columns alphabetically by the contents ...
1
vote
1answer
25 views

Is entry in colum a (at least once) related to entry in colum b?

I'm stuck with a seemingly simple spreadsheet (LibreOffice) problem. Assuming I have three columns given as Name, Day and Mood; how can I find out if person x was happy at least once? In a second ...
0
votes
2answers
48 views

Google Spreadsheet/Excel filtering

As you can see in this image I have spreadsheet of many athlets, that will participate in competition. My task is using groups (or ranges) DISCIPLINAS (en. Disciplines), which contains 100m run, ...
1
vote
1answer
34 views

Convert tab separated to a human readable aligned column headers

Let's say I have a spreadsheet with the following example data: I need to display this in plain text but still human readable. When saved in a tab separated format (tsv) it will output something ...
0
votes
2answers
187 views

Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed. However I can't delete it in the excel Name Manager: Any suggestion why I cannot delete it? ...
0
votes
0answers
45 views

Search and replace with different formatting style

In LibreOffice or OpenOffice Calc spreadsheet, is it possible to search-replace while changing formatting of the replacement text to subscript or superscript at the same time? I have a spreadsheet ...
3
votes
3answers
875 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 ...
1
vote
2answers
1k 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
5answers
1k views

Good tools to visualize spreadsheets?

I have a few CSV and XLS files that I would like to analyse or "visualize" online (like in charts etc). I tried Google Fusion Tables, but I want to know if there are any alternatives. Also suggest ...
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
0answers
26 views

Open Office checking values from one column in a sheet to another

There are few answer similar to my question, but not quite. I have let's say column A, that are names, and column B, that are dates, in one sheet. In another file I want to have same column A (names) ...
0
votes
0answers
19 views

export marked rows in Excel

How can I export the subset of all rows marked by an x in column A? Example data set: A B ---------- 1 apple 2 x orange 3 x lime 4 cherry 5 x lemon Exported data set: A B ---------- 1 ...
1
vote
2answers
8k 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
2answers
24 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
0answers
19 views

Hide rows that have a zero value

I've got large spreadsheet with over 4000 rows. In column G I have a sum. If that sum is 0 I want to hide the entire row. Conditional formatting doesn't seem to do the job. Is it possible to hide ...
0
votes
1answer
93 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 ...
1
vote
1answer
25 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
0answers
48 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 ...
0
votes
1answer
67 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 ...
0
votes
2answers
272 views

How to format excel cells that show duration in years and months?

I can't figure out how to do custom formating that would format input "1/5" as "1 year, 5 months", or "5,0" as "5 years". I do want the cell to retain original input of "1/5" but visually display "1 ...
0
votes
1answer
406 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 ...
0
votes
0answers
52 views

What kind of software would I use for tracking/managing dues payments?

I'm a member of a small-but-not-too-small organization, in which members pay monthly dues. At the moment, our treasurer uses a simple Excel spreadsheet with no automation for tracking dues, with some ...
0
votes
1answer
53 views

How do i change value in filtered cells?

Please could someone help me? I have a spreadsheet full of different vacancies and they go in order of the date advertised but have different departments within that and I need them numbered. I only ...
0
votes
1answer
43 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 ...
0
votes
1answer
46 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 ...
4
votes
1answer
191 views

Allowing users to unhide (format) only chosen columns

TL;DR, what I want to achieve: Hide some columns which I do not want to show to anyone (check) Hide some columns which I want to show only to some users (check) Set separate passwords for both ...
3
votes
1answer
45 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
0answers
16 views

Software to manage service commitments?

I work with an organization that does a lot of volunteer service work and need a solution to the following problems that I considered using a spreadsheet for but would like additional suggestions. *2 ...
0
votes
2answers
44 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
80 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| ...
0
votes
1answer
174 views

Software to calculate average payments of the house

I want to calculate average payment of the house for every month, the average expense of each month. However, most of the fee is across months, they charge every 3 or 2 months. These are normal bills, ...