# Questions tagged [spreadsheet]

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

**4**

votes

**1**answer

58 views

### libreoffice /open office calc - visualise formula

Let's say I have two alternatives in columns C,D like so:
row 6: sample size
row 7: sample mean
row 8: sample standard deviation
and then want to do a t-Test, which requires (amongst other things)...

**3**

votes

**1**answer

126 views

### Excel: Based on value of cell in column A (three possible values), and cell in column B (integer), determine the value of cell C

I am trying to work out a problem as follows:
Column A: one of three possible values. {apples, oranges, pears}
Column B: an integer.
The value of column C should be calculated based on the previous ...

**3**

votes

**1**answer

284 views

### Sum values in specific rows based on whether the row has a value that belongs to a range

Here's what I'm trying to do:
I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...

**3**

votes

**1**answer

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

**3**

votes

**1**answer

3k views

### Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...

**3**

votes

**1**answer

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

**2**

votes

**1**answer

940 views

### Excel: how to illustrate a budget constraint that depends on more than two factors in a table? Calculate how much of a house you can afford

Imagine that you want to make calculation of how expensive flat/house you can afford to buy. You know how much you can spend on housing per month (100). You also know that there are three factors that ...

**2**

votes

**1**answer

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

**2**

votes

**1**answer

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

**3**

votes

**0**answers

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

**0**answers

442 views

### Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?

I'm using Apple Numbers.
I have a table with data like:
A | B
---------+---------
Spain |
U.K |
Spain |
Germany |
U.K |
Italy |
And I would like to set table B to include ...

**2**

votes

**0**answers

619 views

### Histogram of dates in Google Sheets or OpenOffice

I have a list of dates that I would like create a histogram of.
date
5/21/2014
5/20/2014
9/16/2014
5/28/2014
6/8/2014
6/8/2014
5/28/2014
5/20/2014
8/11/2014
7/19/2014
8/11/2014
The chart's x-axis ...

**2**

votes

**0**answers

179 views

### Unable to enter data into cells in sc

I'm trying to use sc, the classic Unix spreadsheet calculator, mainly because I want to be able to look at some csv files nicely formatted in my terminal. I installed sc 7.16 but am seeing some ...

**2**

votes

**0**answers

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

**2**

votes

**0**answers

182 views

### Need to convert password protected Lotus 1-2-3 v4 file

I have a Lotus 1-2-3 version 4 password protected file that I need to open. However, I don't have Lotus 1-2-3. I have not found a converter that will convert a password protected file. Any ...

**2**

votes

**0**answers

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

**1**

vote

**0**answers

22 views

### In Numbers/Excel how can I do a SUMPRODUCT only on rows matching a specific criteria

Let me explain my spreadsheet a little bit. Here is a good screenshot of it:
I have created this spreadsheet essentially because I have gotten tired of the usability issues of just about every ...

**1**

vote

**0**answers

18 views

### Excel spreadsheet/workbook data connections: Two way communication with applied filter?

So let me pose you guys a scenario.
There are several users all inputting data into a single spreadsheet. This work is initially assigned to these users by a manager who, after the data is input, ...

**1**

vote

**0**answers

73 views

### In Excel, is there a way to autofil formulas that link to other files with information from other cells in the current spreadsheet?

I have a master spreadsheet that collates data from other spreadsheets, and shows this via linked cells - for example, I would have ten identically set out spreadsheets, each showing a different total ...

**1**

vote

**0**answers

61 views

### Show contiguous time periods without data in Libre Calc pivot table, pivot chart?

A minimal example: our table has sales in 2015, 2017, but not 2016.
Date Sales
01/02/15 $500
10/11/17 $1000
You make a Pivot Table:
Data > Pivot Table > Create
Row Fields: Date
Data ...

**1**

vote

**0**answers

88 views

### How can I transfer data between spreadsheets using vlookup if spreadsheet changes each month? Can I set it to auto-update?

I have the above data in two spreadsheets.
I receive the top rows. (raw data)
Then total them using formulas. (middle rows)
Then need to move them into a separate spreadsheet. (bottom rows,
desired ...

**1**

vote

**0**answers

56 views

### Excel on Mac - First worksheet overwritten with the second

So I'm new to Mac and I'm working with a spreadsheet in Microsoft Excel. It has one worksheet in it initially. While I'm working with it, I start putting things into a second worksheet. When I'm done, ...

**1**

vote

**0**answers

212 views

### Dynamic timeline spreadsheet with selective stickiness?

I'm trying to create my first spreadsheet ever, but there's one function that I'm finding very difficult to find guidance for from the internet, just like the title of this post felt a bit clumsy to ...

**1**

vote

**0**answers

67 views

### Excel: Conditional formatting, simplifying retrieving data from a range of rows

I am trying to create a schedule for a limited number of trainings (around 50) that are distributed between a limited number of venues (around 15), and I am looking for a smarter approach than the one ...

**1**

vote

**0**answers

194 views

### Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...

**1**

vote

**0**answers

15k views

### How can I create sub-sheets in Microsoft Excel?

I have a workbook in excel that displays information from multiple processes and each of these processes has multiple sub-processes.
I can display information for each process easily using one sheet ...

**1**

vote

**0**answers

294 views

### How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel.
In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...

**1**

vote

**0**answers

85 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

**0**answers

39 views

### What's going on with my Excel formatting?

I have a spreadsheet in Excel with a few columns that have a different format from the rest of the sheet.
I inserted some new cells (old ones moved to the right). None of the old cells had this ...

**0**

votes

**0**answers

44 views

### Can I create a sheet which sums groups of N rows in LibreOffice Calc?

I have a LibreOffice Calc spreadsheet which has this structure
APPLES ORANGES BANANAS
5 0 10
15 2 12
3 2 105
...

**0**

votes

**0**answers

70 views

### Excel conditional formatting copy in table

Is this known a issue? When I add a new row to my table by inserting email, my conditional formatting does not work properly. As you can see in the picture, only one format works, the other one does ...

**0**

votes

**0**answers

683 views

### Splitting excel sheet into multiple sheets based on two columns

I have a table of 10000 rows X 50 columns. The table is all in one sheet and I am planning to split that sheet into multiple sheers based on values in two columns (columns number 5 and 1, ...

**0**

votes

**0**answers

45 views

### Deleting rows in Excel based on individual date range

I have seven Excel files containing information about admissions of several hundred patients to various hospital departments, and data appertaining to these (length of stay, type of discharge etc.) ...

**0**

votes

**0**answers

15 views

### I cannot modify a password-protected excel spreadsheet despite the only other user with the file open is viewing it as “Read Only”

Title. Even though another user does not have the password to modify the spreadsheet, their viewing of the file is preventing me from getting to the password screen and is forcing me into "Read Only" ...

**0**

votes

**0**answers

54 views

### Pulling data from a range of spreadsheets into a single spreadsheet (where sheet names change)

At school we have various spreadsheets to track the progress of our students within their different subjects. To allow us to keep track of the progress we always have a sheet named after the current ...

**0**

votes

**0**answers

62 views

### Find first occurrence of a small sequence from a long list in Excel

I have a long list of words in separate cells in a row
in an Excel spreadsheet.
For example
A B C D E F G
blue red yellow blue green red purple ...
I ...

**0**

votes

**0**answers

31 views

### Data Entry Beginner Asks: How do I 'section' out rows of a spreadsheet to belong to a product/item/object?

I am a complete beginner to data entry and wanted to know how to accomplish the following with the sample dataset provided below.
Search through quantity column for "equal to or greater than value of ...

**0**

votes

**0**answers

46 views

### libreoffice calc - compute range based on boolean values

Let's say I have a range
OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($BD:$BD,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1)
And let's say I have three boolean fields named ...

**0**

votes

**0**answers

60 views

### libreoffice calc - vary column in indirect

Assume sheets that have some values on columns B-K.
Each sheet has
R2=AVERAGE(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))
R3=STDEV(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))
for, say,
O2=...

**0**

votes

**0**answers

47 views

### How to create an automatically updating alphabetical list of unique occurrences in a range of cells?

To illustrate, let's say we have a range of 'sheet1'!$B$2:$F$8 that looks like:
Dennis Charlie Frank Dennis Aaron
Charlie Frank Erik Aaron Charlie
Aaron Dennis Charlie ...

**0**

votes

**0**answers

477 views

### How to check the values are increasing or decreasing between different columns?

In the table below each row has a different behavior. Note that, In the first row, the columns "pool1" the values are decreasing. Also decreasing for columns "pool2" and "pool3".
In the second row ...

**0**

votes

**0**answers

32 views

### Map 2 spreadsheets

I have two spreadsheet.
Both spredsheets have an email field. Spreadsheet 1 has 400 email fields and the other has 1000 email fields. The 400 emails are in the 1000 email spreadsheet. The 1000 email ...

**0**

votes

**0**answers

49 views

### Which versions of MSExcel support the 'WEBSERVICE' function?

Pursuant to Excel formula to take out quotations ,
I saw that it's possible to use a form of web-querying in certain versions of MSExcel. Incidentally, what I saw was:
=WEBSERVICE("http://finance....

**0**

votes

**0**answers

191 views

### LibreOffice SpreadSheet Conditionally copy value from one cell into a second cell?

I'd like to have the calculated value of a cell be carried over to another cell if the result is less than a given value.
For instance, cell A2 is defined as =A1*.75
If the result is less than x, I'd ...

**0**

votes

**0**answers

472 views

### LibreOffice calc; is it possible to automatically make cells coloured according to the HTML code entered into them?

I am creating a LibreOffice calc spreadsheet designed to be basically be a customized version of the table entitled "Default element colors, by atomic number:" on this page http://jmol.sourceforge.net/...

**0**

votes

**0**answers

47 views

### Prepend text for spreadsheet

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...

**0**

votes

**0**answers

219 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

**0**answers

544 views

### enteries in column are automatically changed to upper case letters

I am very new with Open Office and spread sheets in general. In a column whenever I add a new entry it changes all the letters to upper case. Is there a way to disable this? If not, many entries ...

**0**

votes

**0**answers

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

**0**

votes

**0**answers

43 views

### Chart from groupings

Given such a spreadsheet:
col1 | col2 | col3
-----+------+----
alfa | foo | 10
beta | foo | 20
gamma| bar | 12
delta| bar | 42
I'd like to generate a chart grouping values from col2 and ...