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

**0**

votes

**0**answers

3 views

### Google Sheets Suffix To Column Cells [migrated]

I'm editing a spreadsheet of product data that has a category column. I'd like to create a new "Left Handed" as well as "Right Handed" category in addition to each products current categories.
I've ...

**0**

votes

**1**answer

15 views

### I want to create a format in Excel to trigger a colour code?

Currently I have a drop down list on certain cells, connected to a list of statuses. 'Complete' which marks as green, 'Pending' which marks as orange, 'Outstanding' which marks as Red. I want each ...

**-5**

votes

**0**answers

36 views

### Inverse function of COUNTIFS formula

I need to fill five rows and five columns with data, is it possible to fill just one row, and one column, then multiply them by five using a formula?
If possible, which formula?
Can I (using ...

**1**

vote

**1**answer

17 views

### How to show a value of a Cell as it's displayed

I have a cell A2 that shows the value 001. I want to concatenate that value of 001 to a string to show REQ-001. The cell that I want to show this string I have =CONCATENATE("REQ-",A3). But the ...

**1**

vote

**1**answer

21 views

### spreadsheet: sorting by the first n characters of the entries of a column then by another column

Is there any way to sort an OpenOffice Calc spreadsheet by the first n characters of a given column then by another criterion?
For example suppose that we have the following 4*2 spreadsheet (Note ...

**0**

votes

**0**answers

24 views

### Dynamic Excel page with many referances

I want to build a dynamic spreadsheet. I have a blank template for entering weekly information about different locations. Once this information is inputted it is saved as a new sheet and titled for ...

**0**

votes

**2**answers

41 views

### How to sort columns in Excel so that only one value appears in each row?

Say I have a a spreadsheet with the following data:
A B C D
---------------------
2 1 0 2
3 2 1 4
5 3 3 6
7 5 6 8
11 8 10 10
...

**1**

vote

**1**answer

32 views

### How to perform a function based on surrounding cells in Excel?

My rows on my spreadsheet look like the following:
foo | cherry | 400
bar | lemon | 300
foo | cherry | 500
bar | cherry | 70
I want another row to make calculations in column C based on the ...

**-1**

votes

**0**answers

41 views

### How do I use VLOOKUP or other lookup function to return all results of criteria?

I have a spreadsheet like this:
**Column A:** **Column B:**
apple 1 apple 1/red
apple 1 apple 1/green
apple 1 apple 1/yellow
apple 2 apple 2/blue
...

**1**

vote

**1**answer

36 views

### Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works:
=SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000")))
where Financials is a named range. If I ...

**1**

vote

**2**answers

37 views

### add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000
I do not want to use the click and drag box method to apply it across multiple cells/rows.
I know I have to type in the formula bar B1:B40000 but I ...

**0**

votes

**1**answer

49 views

### How do you do a conditional format for a date range in Google docs?

I have a list in a spreadsheet of upcoming game releases. I'd like to do conditional formatting on the Release Date field so that if the game is being released this month / week, I can have different ...

**1**

vote

**1**answer

22 views

### How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape.
This can be done in Excel, but in OpenOffice when i set ...

**0**

votes

**1**answer

41 views

### Rolling Averages sheets in excel

I'm collating a document for a Daily Production report at work, in which every day, a new tab is added and the information on part production from each department for the previous day is recorded. The ...

**0**

votes

**0**answers

6 views

### How can I select that element from a vector of Gnumerc / Excel of which the difference of a given cell is minimal?

I would like to do a goal seek in gnumeric / Excel. I would also like to make a column of data, which values are selected from a vector (range) based on minimal difference to a given cell. What ...

**2**

votes

**0**answers

27 views

### In Excel, Can I have two rows of (worksheet) tabs for my workbook? [duplicate]

I'm using Excel (2013). I have a workbook with numerous different worksheets, and would like to have them take up two rows of worksheet tabs rather than just one. Can I achieve this somehow? I can't ...

**0**

votes

**2**answers

46 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 ...

**0**

votes

**1**answer

39 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

**1**answer

79 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?

**0**

votes

**1**answer

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 | ...

**1**

vote

**1**answer

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

**2**answers

63 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

**1**answer

62 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 ...

**-4**

votes

**2**answers

247 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

**1**answer

37 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

**2**answers

506 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

**0**answers

51 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 ...

**0**

votes

**1**answer

49 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 ...

**0**

votes

**0**answers

31 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

**0**answers

20 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 ...

**0**

votes

**2**answers

25 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

**0**answers

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

**1**answer

123 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 ...

**0**

votes

**0**answers

53 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 ...

**-1**

votes

**1**answer

96 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

**2**answers

481 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

**1**answer

581 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 ...

**1**

vote

**1**answer

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

**0**answers

63 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

**1**answer

69 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

**1**answer

49 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

**1**answer

50 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

**1**answer

264 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 ...

**0**

votes

**0**answers

17 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

**2**answers

54 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 ...

**-1**

votes

**1**answer

85 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| ...

**3**

votes

**1**answer

51 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 ...

**2**

votes

**0**answers

44 views

### How do I import multiple csv files in gnumeric?

I am trying to import multiple csv files into gnumeric so that each one is a separate sheet in the workbook. This is relatively simple in Excel but I cannot work out how to do it in gnumeric.

**1**

vote

**1**answer

523 views

### Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.

**0**

votes

**1**answer

77 views

### Excel: Copy identical data sorted by date-time to future dates

I apologize in advance if this has been answered before but I couldn't find anything using similar wording to my question.
I have an Excel sheet of one year of data, with a value for every 15 ...