Questions tagged [spreadsheet]

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

180 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
2answers
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
2answers
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
2answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
3answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
3answers
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
2answers
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
0answers
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
0answers
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
2answers
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
3answers
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
0answers
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
1answer
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
0answers
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
3answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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 ...