Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [spreadsheet]

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

4
votes
1answer
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
1answer
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
1answer
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
1answer
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
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 ...
2
votes
1answer
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
1answer
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
1answer
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
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
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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 ...