# Questions tagged [spreadsheet]

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

180
questions with no upvoted or accepted answers

**3**

votes

**1**answer

66 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

440 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

**0**answers

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

**3**

votes

**1**answer

91 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

547 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

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

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

83 views

### Chromebook - Sort and Filter in Google Sheets while Offline

I have spreadsheets stored offline on a Chromebook that I would like to sort and filter without needing to go online.
They were created by downloading from Google Sheets, but when I open them locally ...

**2**

votes

**1**answer

3k views

### Excel - compare three cells, if same then something, if not then something

=IF(F9=G9=B9; H9*C9;"Problem")
This is the formula I'm using, but it always results in "Problem", even when the three cells have same text in them.
I want to compare the cells, and if they are the ...

**2**

votes

**2**answers

2k views

### Copy paste from spreadsheet to browser list

I have a browser based form like the one shown below.
The Student No and Name of the Student fields are not editable.
I need to key-in numbers to the Total field.
The Student No field is numeric. ...

**2**

votes

**1**answer

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

39 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

**0**answers

186 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

216 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

202 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

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

**2**

votes

**2**answers

2k views

### Data validation - populating a drop down menu using a defined name

I am using data validation to create a drop down list on a worksheet. What I'd like to be able to do is set up a list of values as a defined name (Insert > Name > Define...) and then enter that ...

**1**

vote

**2**answers

44 views

### Excel: How can I find all the occurrences of the matching columns

Consider I have the following table (unsorted):
Name
ID
Account
John Doe
1
1234
John Doe
1
6524
Jane Doe
2
6346
Jane Doe
2
1241
Jane Doe
2
3634
And another one like this:
Name
Account 1
...

**1**

vote

**2**answers

33 views

### Need to simplify date entry in Excel spreadsheet

Every month I need to do a large spreadsheet for billing purposes.
Among other columns I have one start date and another for end date, formatted as dd-mmm.
To enter this date in the spreadsheet for ...

**1**

vote

**0**answers

8 views

### Searching indexed spreadsheets from Windows 10 explorer “search”

I have a client that used to use Windows 7, and was able to search directly from the Windows explorer (start menu) to find spreadsheets by searching a number that was on column B within. Now that they'...

**1**

vote

**1**answer

383 views

### How do I fill a row based on the number of filled columns in the row above?

Here's my current spreadsheet:
Rows 1 and 3 are the input, and I want to fill row 4 automatically based on how many columns there are in row 3. Example end result:
The "discount factor" is ...

**1**

vote

**1**answer

58 views

### Counting from & to specific dates & times on a spreadsheet - mostly done, but some bugs

I wanted to make a spreadsheet that I could enter a date & time and calculate how much time (in years/dates/months/weeks/hours/minutes/seconds...)
I kept expanding on it. it serves very little ...

**1**

vote

**0**answers

20 views

### How to quickly select items that meet a criterion on excel

I have two excel spreadsheets. One has hundreds of names and emails (the names aren't sorted properly and include people who will not be receiving an email), and another with hundreds of names that ...

**1**

vote

**0**answers

13 views

### finding difference in two columns of a spread sheet?

lets say I have 3 columns
A B C
1 2
2 3
4 5
and i will go on entering numbers in A and B, in C I need to have the difference between col B and col A, can I write a single formula for ...

**1**

vote

**0**answers

13 views

### How do I turn off autoformat OpenCalc / OpenOffice Spreadsheets

Without context and background: Read the title, How do I turn off AutoCorrect in OpenCalc, specifically time, preferably turn off all AutoFormat.
I am using a spreadsheet to track how fast my online ...

**1**

vote

**1**answer

93 views

### How to fit a polynomial trendline such that only the leading two coefficients are non-zero in Excel?

I have data on the displacement of an object over time and a cubic trendline fits it very well.
I know that the initial displacement is 0 so I can tick the "set intercept" box.
However, I also know ...

**1**

vote

**0**answers

51 views

### What is the VBA equivalent of using an array formula to calculate the max non-blank value of a range?

I have the following array formula in Excel: {=MAX((C9:C44<>"")*(ROW(C9:C44)))} that calculates and returns the row number of the last non-blank cell in a range.
I could reference the result in ...

**1**

vote

**3**answers

11k views

### Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet).
Example: I have a sheet containing rows of names which ...

**1**

vote

**1**answer

64 views

### Open a spreadsheet with broken formulas

I am using code the generate a spreadsheet, including a formula for a cell on certain rows. When I do this, there is something wrong with the formula, and when I try and open the spreadsheet, Excel ...

**1**

vote

**0**answers

76 views

### Excel INDEX MATCH across 2 sheets & Update data with VBA

I would like some help with the following tasks.
I will in the future add an additional sheet with 3 columns. A would be a date, B a Wo number and C would be the date which the Wo was closed by ...

**1**

vote

**0**answers

506 views

### Sum cells within row range of merged cell in Excel

I am trying to setup an automatic formula that would sum values. Specifically the formula needs to sum all cells in a row that falls within a merged cell.
The scenario is quite simple, I have an ...

**1**

vote

**0**answers

5 views

### How to sum up products like this?

I am creating a spreadsheet to keep track of large expenses. The idea is that I virtually finance things rather than actually do it that way when budgeting.
The idea is that if I make a large ...

**1**

vote

**0**answers

133 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

91 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

**3**answers

322 views

### Spreadsheet: create column based on one column sorted by values of another

I want to create a new column of data from one column sorted by another column.
It's easiest to explain with an example. This is not actually what I'm doing, but I think it explains it well.
Let's ...

**1**

vote

**2**answers

67 views

### Automate completion of spreadsheet for invoice

I need to invoice someone each week. I would like to create a template as 90% of the information on each invoice is the same, and the remaining 10% follows simple rules.
Each invoice has a number ...

**1**

vote

**0**answers

96 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

86 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

**2**answers

3k views

### remove duplicate values from two spreadsheets in excel

I have got two different spreadsheets with some duplicate emails in it but maybe with different information in other cells of the same line. Both of the spreadsheets contain more than 10k lines. I ...

**1**

vote

**3**answers

125 views

### How can I copy the formula used in a cell to another cell as a literal formula

Cell A1 has the value 1.
Cell A2 has the value 2.
Cell A3 has the formula entered " = A1 + A2" and shows the value 3.
Similarly Columns B1,B2, B3, C1, C2, C3 etc.. etc..
I want to copy the formula (...

**1**

vote

**0**answers

58 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

**1**answer

733 views

### Excel - Compare rows content and flag up differences in another cell

I'm trying to work this one out:
Shift content is the routes that must be taken on a particular shift
Driver knowledge is the routes that a particular driver knows
Each day they get allocated a shift....

**1**

vote

**0**answers

236 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

**3**answers

224 views

### Excel “If Then” formula

I'm trying to write a formula which says "if sum of cell F24 to I24 plus sum of cell L6 to L9 sum up smaller than 50, show value in cell L9 in cell N9, and if not, subtract sum of cell F24 to I24 plus ...

**1**

vote

**0**answers

84 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

**1**answer

518 views

### Find highest number of consecutive outcomes for each person in list

I've got an Excel list that looks like this (with the name in column A and the score in column B):
N. Nam Score
1. Jim 7
2. Jim 4
3. Jim 4
4. Jim 7
5. Jim 7
6. Jim ...

**1**

vote

**0**answers

714 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

**1**answer

83 views

### Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?

First I'll explain what I already have (which works), then explain my question, which is about expanding it:
I have two different spreadsheets that will be worked on at the same time, I needed to see ...

**1**

vote

**0**answers

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