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

learn more… | top users | synonyms

0
votes
1answer
24 views

Excel Opening as a blank spreadsheet, but with values

So when we open an Excel spreadsheet that is populated it will open with all of the cells appearing blank. However if you click on any of the cells you can see in the formula bar at the top that they ...
6
votes
3answers
7k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
0
votes
0answers
8 views

Calling IMPORTRANGE inside of REGEXMATCH function? [migrated]

I'm interested to know whether (and how) can i call IMPORTRANGE function inside of REGEXMATCH function. I want to get root domains from list of URL's and use REGEXMATCH to do this but want to get ...
0
votes
0answers
22 views

Strip non-numeric characters from a phone number in spreadsheet

I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example: (123)456-7890 fred ...
0
votes
1answer
27 views

Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following: https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing There is a set list of names in Column A. ...
0
votes
2answers
2k views

Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula. problem is the rota is written using a number of different notations for different shifts some are ...
0
votes
0answers
26 views

Excel: Converting two lists into a matrix

I have two lists in my workbook currently Name | System 1 | System 2 | System 3 | John | x | x | | James| | x | x | Peter| | x | ...
0
votes
0answers
14 views

Microsoft Excel 2013 Formatting When Importing Data

I am importing data from Quickbooks into an Excel 2013 spreadsheet and this will take place on a weekly basis with updated information being added to this report. The report is an Open Estimates ...
0
votes
1answer
21 views

How to make a custom selection on excel?

Is it possible to make a custom selection on a spreadsheet and in particular on excel ? If yes, how to do it ? (for example, I would like to select one cell on 3 among a column or select only the ...
-1
votes
0answers
10 views

Copying sheet with references to another sheet using Open Office 4.1.1

For example: In sheet1 in cell A1 I have function =E1. I want to copy sheet1 and I want that in sheet1_2 (copy of sheet1) in cell A1 to be this function: =E1 and not =sheet1.E1 Order of sheets is: ...
0
votes
2answers
36 views

How to remove rows that match?

I have a csv file with list of people (first name, last name, email) and then later on I will get a list of people in another csv file that unsubscribed ( only one column with email). Q: How can I ...
1
vote
1answer
22 views

Spreadsheets for Matlab

I was wondering if there was a way to directly write a spreadsheets in Matlab, or if someone had published a Matlab add-on that specifically introduced spreadsheets?
2
votes
2answers
428 views

How can I combine two lists into one long list?

I have two lists in coloumns 1 and 2. I need coloumn 3 to be of the format one dash two. For example: Column C will need to repeat all values of A and B. I would prefer a formula for this is ...
4
votes
1answer
815 views

Edit spreadsheet object in Word document with protection

I am trying to create a template in Word to be used on a SharePoint site. Using Word 2003, is there a way to enable protection on a document that has an Excel Spreadsheet (97-2003) object on it but ...
3
votes
1answer
5k 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 ...
0
votes
0answers
19 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
17 views

How do I add a number to an input cell?

The scenario is that cell A1 is an input cell, and I want to add another cell's value to what is entered in the A1 cell. For example, A1 is the input cell. A2 is the output of some independent ...
1
vote
1answer
364 views

Unlink chart from data series in OpenOffice Calc

Is it possible to create a chart on a data series in Calc, and then unlink it so that if I sort that same data a different way, my chart wont change? i am trying to show some trends based on several ...
1
vote
2answers
97 views

Parsing dates in LibreOffice Calc - is there a better/easier way?

I have a string of this particular format: DD/MMM/YY HH:MM AM/PM e.g. 03/Nov/14 9:00 AM I could not find figure out how to make Calc recognize it as a date so I had to do the following for a ...
2
votes
1answer
4k views

Split Text into Columns Function in Excel

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number. QA32 Product One (Large) 23 AZ63 Product Two 96 PR65 Product Three 149 How do ...
0
votes
2answers
15 views

Can I have multiple views of spreadsheet data which extract and sort from a larger set?

Supposing I have a spreadsheet (Libreoffice ideally but Excel is ok) which has data like this ______| Age | Height | Shoe Size | etc... Adam | 14 | 180 | 11 | ... Beth | 21 | 205 | ...
2
votes
0answers
29 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 ...
0
votes
0answers
19 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 ...
0
votes
1answer
14 views

Importing numbers with leading zeros as text strings in gnumeric

I have a csv file with numbers, some that start with zeros. Those zeros are somewhat important to me. All the entries in the file are encapsulated with double quotes. Gnumeric's import dialogue ...
0
votes
2answers
84 views

How do i split a single row of columns into multiple rows and columns?

I've looked all over and either I am not sure how to word this for searching, or there just isn't anything out there. Originally, the information in my excel sheet was pulled from a text file. I had ...
1
vote
2answers
7k views

How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1 Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value. I don't want the C-column. ...
2
votes
1answer
3k views

How do I use conditional formatting in a spreadsheet based on dates?

I have a spreadsheet in OpenOffice V3.3.0 (used in Windows 7 32 bit). One column contains dates (in the format: 14 August 2011 ) and I want to use conditional formatting on those cells depending on ...
0
votes
4answers
32 views

Inserting specific number of rows in Excel

I, for the life of me, can not figure out why M$ has not provided this feature. If I want to insert 597, for the sake of argument lets say blank, new rows, all available information tells me to either ...
0
votes
1answer
2k views

Make cells automatically shrink to fit text in OpenOffice spreadsheet

Is there a way to get the widths of cells to automatically shrink themselves to fit the longest line in a column?
1
vote
2answers
37 views

How to export a table from inside a PDF into a spreadsheet?

I want to convert this PDF which contains a table, into an Excel-compatible spreadsheet for the purposes of making flash cards. Does anyone know of an automatic way to do this without manually ...
0
votes
0answers
24 views

Formatting a chart with many data points for a small display area

In Excel 2013, I need to display a chart with dates on the X axis and errors (already computed) on the Y axis. The problem is that I have 916 data points taken every six hours spanning approximately ...
0
votes
1answer
42 views

Identify in what of the given ranges is a random number

I have this table with the first column being just an Identifier Code and the second one being the ranges. So for example if a number is between 0 and 1134, it corresponds to the first row ...
0
votes
1answer
29 views

organizing columns in excel into standard format

I have data in a excel spreadsheet in a regular pattern that needs to be organized in a format friendly to statistical analysis. Here is the format: Clinton 29.30% 22 Edwards ...
1
vote
4answers
463 views

Count the number of rows with equal values in two specified columns

I have two columns in MS Excel/LibreOffice Calc/Google Spreadsheets with numbers. I would like to count the number of rows which have identical values in both columns. In the following example: ...
0
votes
0answers
19 views

Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea. http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html ...
0
votes
1answer
224 views

Open Office Calc formating table

Instead of data, I would like the first row to be the names of the columns. How can I do this? One of the reasons I want this is so that if the rows are rearranged the names wont be jumbled up. I ...
0
votes
1answer
59 views

Can I separate email id in spreadsheet based on domain in columns? [duplicate]

I have a list of 20000 email id which have emails from domains like gmail, ymail, gmx, hotmail, custom domains etc I have those list in a spreadsheet Column A. Can you help me separate them according ...
0
votes
0answers
36 views

Label distribution and scaling in Numbers

I'm currently working in OS X Numbers on a document with two columns and 1078 rows. Using this data, I have constructed a simple 2D line graph which I am fairly happy with. The issue that I'm having ...
2
votes
1answer
586 views

Import one cell of data from one spreadsheet to another

I have one spreadsheet that is for global use in which I would like to incorporate one cell of data from various other spreadsheets to refresh anytime there is a change to the source. So for example: ...
0
votes
2answers
52 views

How to add up hours

I have a spreadsheet to track my hours at work and I was wondering how I would go about adding up my total hours. I'm not fussed what format it be in, whether it be HH:MM or as a decimal. ...
5
votes
2answers
644 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
0answers
13 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 ...
3
votes
2answers
11k views

How to break a spreadsheet containing CSV into multiple columns?

I'm working on preparing data for export, and the client has put all of the data into a spreadsheet. Each field of data should be its own column. The problem is that the client erroneously put ...
6
votes
2answers
4k views

How to view large spreadsheet in Windows

What would you recommend to a non-technical Windows user for viewing (no need to edit) large spreadsheets (200MB - 5GB). Format is CSV and I am told Excel chokes.
-2
votes
1answer
46 views

What formula can I use to colouring in columns?

I am creating a planning spreadsheet for the operators to use on the shop floor. Products have a specific time in which they are made. All I want to do is, when I type a product name into ANY cell, ...
1
vote
1answer
954 views

Filtering by currency in Excel

I have this situation in Microsoft Excel: Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...
1
vote
2answers
1k 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
1answer
122 views

LibreOffice Calc time formatting trouble

I'm calculating a time sheet with LibreOffice with this SUM column. 00:00 08:00 08:00 08:00 08:00 08:00 00:00 04:00:00 PM The printing format of the bottom row is Time 876613:37:46 but that's not ...
0
votes
1answer
86 views

Copy reference instead of value/formula in Excel?

I have some cells from one table I which I want to reproduce in another table, initially in the same order (but later, I'll mess this order up by moving cells around). For the sake of discussion ...
1
vote
1answer
39 views

How to efficiently reference an entire row in Excel?

I am working on a budget spreadsheet in which I have to pull in cell data from another external spreadsheet. I have to do this for several different sheets with massive amounts of data in the sheets, ...