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

learn more… | top users | synonyms

0
votes
0answers
8 views

Comparing two columns in Google Docs [migrated]

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns: A (Breed) | B (Male) | C (Male Goal) | D (Female) | E ...
-2
votes
1answer
32 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
22 views

Excel - auto-fill blank cells or 0 in B:B to a dummy e-mail address which changes sequentially

I am creating a database in Excel that requires an e-mail address to be assigned to each row or user. While some users have an e-mail address in the original CSV file the majority do not. Each e-mail ...
0
votes
1answer
36 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
31 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, ...
0
votes
1answer
25 views

Unable to expand cells in Microsoft Excel

I have an odd issue that I need your help with. Within my spreadsheet there are many cells that although contain data, do not respond when I try to automatically resize them. I've tried auto-fit, ...
0
votes
1answer
47 views

Spreadsheet data checking columns

I am trying to separate datas from columns(A,B&C) based on datas from an index table at columns (E&F) I need to list only those data from columns A/B/C when column B data has no corresponding ...
0
votes
2answers
28 views

Double column value in a spreadsheet

I need to double the value of a column (ex: C) into another (ex: F) for every row in a spreadsheet. How can I achieve this? This is probably simple, but I can't get my head around it. Thank you.
0
votes
1answer
30 views

Combining Two Differently Formatted Spreadsheets

I'm relatively new to excel and am having some issues comparing two sheets of data. We're looking at the benefits over the fiscal year by month. One spreadsheet has the project ID on the left, and the ...
0
votes
1answer
26 views

I want to create a format in Excel to trigger a colour code?

Currently I have a drop down list on certain cells, connected to a list of statuses. 'Complete' which marks as green, 'Pending' which marks as orange, 'Outstanding' which marks as Red. I want each ...
2
votes
1answer
22 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 ...
1
vote
1answer
29 views

spreadsheet: sorting by the first n characters of the entries of a column then by another column

Is there any way to sort an OpenOffice Calc spreadsheet by the first n characters of a given column then by another criterion? For example suppose that we have the following 4*2 spreadsheet (Note ...
0
votes
2answers
70 views

How to sort columns in Excel so that only one value appears in each row?

Say I have a a spreadsheet with the following data: A B C D --------------------- 2 1 0 2 3 2 1 4 5 3 3 6 7 5 6 8 11 8 10 10 ...
1
vote
1answer
44 views

How to perform a function based on surrounding cells in Excel?

My rows on my spreadsheet look like the following: foo | cherry | 400 bar | lemon | 300 foo | cherry | 500 bar | cherry | 70 I want another row to make calculations in column C based on the ...
1
vote
1answer
95 views

Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works: =SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000"))) where Financials is a named range. If I ...
1
vote
2answers
43 views

add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000 I do not want to use the click and drag box method to apply it across multiple cells/rows. I know I have to type in the formula bar B1:B40000 but I ...
1
vote
1answer
34 views

How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape. This can be done in Excel, but in OpenOffice when i set ...
0
votes
1answer
47 views

Rolling Averages sheets in excel

I'm collating a document for a Daily Production report at work, in which every day, a new tab is added and the information on part production from each department for the previous day is recorded. The ...
0
votes
0answers
7 views

How can I select that element from a vector of Gnumerc / Excel of which the difference of a given cell is minimal?

I would like to do a goal seek in gnumeric / Excel. I would also like to make a column of data, which values are selected from a vector (range) based on minimal difference to a given cell. What ...
2
votes
0answers
27 views

In Excel, Can I have two rows of (worksheet) tabs for my workbook? [duplicate]

I'm using Excel (2013). I have a workbook with numerous different worksheets, and would like to have them take up two rows of worksheet tabs rather than just one. Can I achieve this somehow? I can't ...
0
votes
2answers
52 views

Pulling out data from a spreadsheet

I need to add information of clients who have visited the office, which department, who they were referred by, who they were referred to etc. I have linked a screenshot of what I have currently made ...
0
votes
1answer
45 views

Multiple users editing the same Spreadsheet, is it possible? [closed]

I have used a piece of software called Prezi for multiple users to edit a presentation at the same time, I find this a very helpful tool as if lots of data is distributed across a number of minds it ...
0
votes
1answer
178 views

How to get plain text, colon separated file into spreadsheet with data separated into columns

I received a digest of registrations for events. The fields are separated by colons and in plain text. Can I copy and paste this info into libreoffice spead and get it displayed in columns?
0
votes
1answer
16 views

Remove symbols while appending in excel

I'm having some parsing issues here. I have a column of Twitter handles, with '@' in front of them, and I'm trying to auto-append them in a URL on the next column. Example: | @Bob | ...
1
vote
1answer
26 views

Is entry in colum a (at least once) related to entry in colum b?

I'm stuck with a seemingly simple spreadsheet (LibreOffice) problem. Assuming I have three columns given as Name, Day and Mood; how can I find out if person x was happy at least once? In a second ...
0
votes
2answers
87 views

Google Spreadsheet/Excel filtering

As you can see in this image I have spreadsheet of many athlets, that will participate in competition. My task is using groups (or ranges) DISCIPLINAS (en. Disciplines), which contains 100m run, ...
1
vote
1answer
88 views

Automatically sorting a range of cells after one cell within the range has been modified?

I have a number of named vertical ranges on a spreadsheet, containing lists of names. New names are usually manually added at the very end of each list. Obviously, this means that unless manually ...
0
votes
1answer
62 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 ...
-4
votes
2answers
513 views

Custom Data Validation in Excel 2010 [closed]

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use? ...
1
vote
1answer
41 views

Convert tab separated to a human readable aligned column headers

Let's say I have a spreadsheet with the following example data: I need to display this in plain text but still human readable. When saved in a tab separated format (tsv) it will output something ...
0
votes
2answers
1k views

Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed. However I can't delete it in the excel Name Manager: Any suggestion why I cannot delete it? ...
0
votes
0answers
61 views

Search and replace with different formatting style

In LibreOffice or OpenOffice Calc spreadsheet, is it possible to search-replace while changing formatting of the replacement text to subscript or superscript at the same time? I have a spreadsheet ...
0
votes
1answer
54 views

How to prevent all-filtered-out row label values from appearing in a pivot table?

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns: Name: A string. CountMe valued either 0 or 1. Year an integral number. Tokens the data we're ...
0
votes
0answers
35 views

Open Office checking values from one column in a sheet to another

There are few answer similar to my question, but not quite. I have let's say column A, that are names, and column B, that are dates, in one sheet. In another file I want to have same column A (names) ...
0
votes
0answers
21 views

export marked rows in Excel

How can I export the subset of all rows marked by an x in column A? Example data set: A B ---------- 1 apple 2 x orange 3 x lime 4 cherry 5 x lemon Exported data set: A B ---------- 1 ...
0
votes
2answers
25 views

keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence? The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...
0
votes
0answers
19 views

Hide rows that have a zero value

I've got large spreadsheet with over 4000 rows. In column G I have a sum. If that sum is 0 I want to hide the entire row. Conditional formatting doesn't seem to do the job. Is it possible to hide ...
0
votes
1answer
184 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
0answers
62 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 ...
-1
votes
1answer
148 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
0
votes
2answers
804 views

How to format excel cells that show duration in years and months?

I can't figure out how to do custom formating that would format input "1/5" as "1 year, 5 months", or "5,0" as "5 years". I do want the cell to retain original input of "1/5" but visually display "1 ...
0
votes
1answer
823 views

Disable prompts in Microsoft Excel when saving a CSV file [duplicate]

I have to download multiple csv reports from the Internet throughout the day, make a tiny change to them, and save the change. Excel adds 3 completely unnecessary dialogs to what should really be a ...
1
vote
1answer
27 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
0
votes
0answers
83 views

What kind of software would I use for tracking/managing dues payments?

I'm a member of a small-but-not-too-small organization, in which members pay monthly dues. At the moment, our treasurer uses a simple Excel spreadsheet with no automation for tracking dues, with some ...
0
votes
1answer
102 views

How do i change value in filtered cells?

Please could someone help me? I have a spreadsheet full of different vacancies and they go in order of the date advertised but have different departments within that and I need them numbered. I only ...
0
votes
1answer
52 views

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}?

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}? My intention is to calculate a vehicle's running average ...
0
votes
1answer
57 views

IF (AND () function does not work as it should be

When I write the following simplified command in a cell, =IF(AND(C6="A";D6="30");D2;0) it does not appear the value of the cell D2 but zero, depsite the fact that the 2 assuptions of IF(AND) are ...
4
votes
1answer
381 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
17 views

Software to manage service commitments?

I work with an organization that does a lot of volunteer service work and need a solution to the following problems that I considered using a spreadsheet for but would like additional suggestions. *2 ...
0
votes
2answers
60 views

Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that: =SUM(A1:INDEX(...)) Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function. The ...