# Questions tagged [spreadsheet]

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

**0**

votes

**1**answer

37 views

### Excel - Search for a string then add some values (Entered to many arguments)

I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.
I was getting #NA as a ...

**0**

votes

**0**answers

18 views

### In Google Sheets, how can I use SUBTOTAL with an IF argument? [migrated]

My N column data is either "Win" or "Loss", now I'm trying to count them separately with SUBTOTAL because I utilize filters quite often.
I'm trying something like this:
=SUBTOTAL(103, IF($N18:$N, "...

**0**

votes

**0**answers

4 views

### Are permissions granted by IMPORTRAGE bi-directional or one-way in Google Sheets? [migrated]

If I want to import "Sheet A" from one workbook into "Sheet B" in another workbook by using =IMPORTRANGE() inside of Sheet B - when I grant it access to Sheet A is that permissions only 1 way? Meaning ...

**0**

votes

**0**answers

6 views

### cell reference in OpenOffice sheet name

Is it possible to include cell data in a spreadsheet name? Specifically, can I name a sheet "Savings Account $__", with the dollar amount displaying my current balance drawn from a cell within the ...

**0**

votes

**1**answer

22 views

### Long Calc Sheet - subtotal at bottom of page, transfer to next page?

Assume i have a LibreOffice Calc sheet spanning multiple pages in printout. Is there a way to calculate a subtotal for each page and print it at the bottom of the respective page as well as at the top ...

**0**

votes

**1**answer

219 views

### How to create a Supply & Demand style chart

Here's an example of a basic Supply and Demand Graph:
The Vertical Axis is always Price
The Horizontal Axis is always Quantity
There should be two lines, one for the supply curve and one for the ...

**1**

vote

**3**answers

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

**0**

votes

**1**answer

40 views

### LibreOffice Calc - Multiple INDEX-MATCH and copy to other cells

I'm just starting to use LibreOffice Calc to try to manage my finances, and have imported CSVs of statements from my bank.
The first task I would like to accomplish is to display, in an easy-to-read ...

**2**

votes

**1**answer

54 views

### Excel 2016: Formula to get data from different spreadsheet

help with this tricky formula is greatly appreciated.
='[DATA.xlsm]2018'!$G$6 *this formula works, but . . .
Spreadsheet with formula Spreadsheet with Data
I need the formula to determine ...

**0**

votes

**1**answer

43 views

### How do I create a ''droplet'' script in Win7 that runs a Notepad++ task on a CSV file

I have recently asked a question on how to perform a neat little 'Find & Replace' task on my CSV media database files (a task i have to do at work on a daily basis). The question was answered here ...

**0**

votes

**1**answer

27 views

### how to use Countifs function Excel?

from what i have been reading online it appears Countifs function is what i need to use for my problem. I have data that looks like this and goes on for several months:
|Month |Day| Time ...

**1**

vote

**2**answers

50 views

### How do I copy specific selected data between cells in a CSV file in Excel or Notepad++?

I have to download media file databases on a daily basis and modify metadata from a csv file so that the unique numerical prefix in column B is added as a suffix to the cells in column C.
Is there a ...

**1**

vote

**3**answers

769 views

### How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text

Every time I copy-paste some numerical data written as fractions or separated by hyhens into Excel, it always converts numbers that could possibly be dates into dates. There's no way of getting them ...

**0**

votes

**1**answer

45 views

### spreadsheet formula for returning parts of filtered cells in column

I use Libreoffice. (If Excel or GoogleSpreadsheets would be the only way to get a solution, I would be willing to switch. But the preferred platform is LibreOffice, I'm looking for a solution on this ...

**0**

votes

**2**answers

111 views

### How exactly do I make multiple columns into one row to sort?

Currently trying to sort data into alphabetical order, however, I am running into a problem.
Every time I try to sort it the product number doesn't match up with the product.
I have been going on ...

**0**

votes

**1**answer

47 views

### Copying from a table row to spreadsheet with formula

I have this sample data from my HTML table row.
Tester sample@gmail.com 2018-07-19 1000 1200 new
And I am copying this data to google spreadsheet with a formula ...

**0**

votes

**2**answers

46 views

### Lookup Function Giving Inconsistent Results in Excel & Google Spreadsheet

I am trying to create a column of looked-up values using the LOOKUP function. The goal is for the formula to look up the "rank" value of each "name" and print it in the "lookup" cell on the same row.
...

**3**

votes

**1**answer

83 views

### A single pound sign '#' to custom format excel cell?

I need to display a very big number within Excel, for example a following number: 174587584558159.
Excel displays it by using a scientific notation, like following: 1.74588E+14.
I tried to format ...

**0**

votes

**1**answer

68 views

### Updating column values from one Excel spreadsheet to another

I had no idea what to Google for this. Basically I have 2 Excel files: File A and File B.
Here is File A:
And here is File B:
I am trying to loop through File B. I want to look at each row's first ...

**0**

votes

**1**answer

45 views

### two date cells - i want 3rd cell to some value

I have two cells with completed installation dates. I have a 3rd cell where I need to input outstanding installations.
If the 2 cells have dates, it means the installation is complete so I want the ...

**1**

vote

**1**answer

50 views

### how to save time with multiple COUNTIFS possibilities?

I am sure that there is a smarter approach then this brute one:
=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
p40<>o40;p40&...

**0**

votes

**1**answer

33 views

### Excel - can you setup a certain worksheet to open when the spreadsheet is first opened?

Hoping someone can help, I have a spreadsheet that I need to open at a certain page, everytime when opened eg.. an index page.
I have the Vb code and works offline but I have since found out this ...

**0**

votes

**2**answers

53 views

### DATEDIF() not returning expected value, how can I calculate age in months?

I'm trying to calculate the age of my credit card accounts in Excel. I've read online to use the DATEDIF() function, however, I'm not getting the expected result and I'm not sure where I screwed up.
...

**1**

vote

**2**answers

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

votes

**1**answer

47 views

### Copy cells from one excel spreadsheet to another based on a value in the row

I have a spreadsheet, Sheet 1, with data in it taken by the minute. Each line row has a timestamp and the corrosponding data follows.
I have a second spreadsheet/sheet, Sheet 2, with accompanying ...

**0**

votes

**0**answers

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

**1**

vote

**0**answers

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

**0**answers

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

**0**

votes

**1**answer

49 views

### Generate cell pairs based on first row and column

Given a spreadsheet like this:
x | pressure | distance | height
pressure
distance
height
I would like an output like this:
pressure pressure
pressure distance
pressure height
distance pressure
...

**-1**

votes

**1**answer

60 views

### Return value pairs based on first row and column

Gnumeric has a nice tool for calculating correlations, but is limited in terms of specifying your output. It looks like this, Figure 8-7 :
Correlations | pressure | distance | height
pressure | ...

**4**

votes

**2**answers

2k views

### WPS Office CSV Delimiter - Linux

I have been using LibreOffice for all my Spreadsheet and Wordprocessing requirements lately but am getting fed up with the performance issues, it has a massive lag and also constantly freezes.
I have ...

**0**

votes

**1**answer

32 views

### Spreadsheet: Split a column if it contains a certain string

Here is an extract from my spreadsheet:
Please note: this is just a simplified example. My full spreadsheet has many columns and over 10,000 rows.
In my spreadsheet, I frequently have rows that have ...

**-2**

votes

**1**answer

49 views

### How to update item quantity in excel?

I don't know much about Excel or spread sheets to be honest. But my girlfriend has been working on updating item quantities for the past two days. There are over 500 items.
Basically what she needs ...

**1**

vote

**0**answers

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

**2**

votes

**4**answers

618 views

### Is it possible to make the cell cursor blink in excel?

I have a large spreadsheet - with quite a bit of colour and bold font etc.
It's often hard to see the cursor - i.e. to find the cell that is selected. It just appears as a slightly thicker green line ...

**1**

vote

**2**answers

78 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

**1**answer

661 views

### Excel is changing my time input (32:00:00 becomes 8:00:00)

I am making a spreadsheet in Excel to keep track of time. When I enter 32:00:00 (hh:mm:ss) on the spreadsheet, it changes my time to 8:00:00. But when it adds up all the time at the bottom it still ...

**0**

votes

**0**answers

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

**8**

votes

**3**answers

2k views

### How to compare two Excel spreadsheets?

I would like to compare two Excel spreadsheets
Both have the same columns, in the same order, but not the same rows.
I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for ...

**0**

votes

**1**answer

27 views

### COUNTIFS with dates

I'm trying to get the number of support tickets for each month. In my first tab "Tickets" I have:
The date values have the following characteristics:
In my second tab I have:
I want to know how ...

**1**

vote

**1**answer

264 views

### Excel, update duplicates automatically

I have a table, something like this:
--------------------------------------------------------------------------------------
|Id | Name | Category | Sub-Category | Supplier
-----...

**0**

votes

**0**answers

22 views

### Converting a mathematical formula into spreadsheet formula in LibreOffice

I got a great answer to a maths question on the maths SO. However, for the life of me, I cannot think how to make use of it in any practical way.
I have a spreadsheet with the values of k and n in ...

**0**

votes

**1**answer

63 views

### Spreadsheet: find a cell keyed by date

This is a question about Apple Numbers spreadsheet program.
Suppose I have two sheets: Benchmark and Training. In the Benchmark sheet, I track a benchmark value over time:
| Date | Benchmark |...

**1**

vote

**2**answers

212 views

### Excel: using a formula to only sum from above row until next blank cell?

I'm a bit of a noob so bear with me. I'm making a spreadsheet of various products bought and sold at various prices. The table is sorted by product, with a blank row separating each complete buy-sell ...

**3**

votes

**2**answers

326 views

### Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)

I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it.
This number must be copy-pasted to a numeric cell in Excel or Calc. The file ...

**0**

votes

**1**answer

43 views

### SUM depending on column value

I have a table that looks like this:
In cell F2 I would like to have the sum of all values in column B where the value in the cell next to it (C column) is equal to "Niels".
How could I do this? (I'...

**1**

vote

**1**answer

34 views

### Incrementing dates after a certain number of entries (in this case 9)

I would like for a date to increment after 9 entries like the below:
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/...

**0**

votes

**1**answer

48 views

### Count value pairs and get average of those pairs?

I have a spreadsheet with events that happened on certain dates. I created an extra column with week numbers based on those dates.
Now I got several events per week (number). What I want to find out ...

**0**

votes

**2**answers

53 views

### How can I make Excel filter each additional column as it's added?

If I highlight the entire first row and filter it, I shouldn't need to repeat the process every time I add a new column. Is there a way around that? I could run a macro, but that's still an extra step ...

**1**

vote

**2**answers

58 views

### Output matched value based on range

I have a spreadsheet with two tabs: one is a search term data from an Adwords campaign and another one is a list of text fragments.
I want to use a function to return check if the search terms ...