# Questions tagged [spreadsheet]

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

602
questions

**0**

votes

**1**answer

17 views

### Excel - sort one spreadsheet by exact order of second spreadsheet based on one column with same data

I trying to find a way how to sort one spreadsheet into the same order like second spreadsheet - based on their one same column for example.
Here's an example what i need, i will make more sense like ...

**0**

votes

**0**answers

15 views

### Excel on notebook

I want to change all numbers in column I to be - (negative)
if the corresponding words in column B are SELL
Example
Col B row 1 “sell” then col I row 1 -123
Col B row 2 “buy” the col I no change
Col B ...

**1**

vote

**1**answer

15 views

### Combine one set of time series data with another when some dates are missing in one data set

I have a LibreOffice Calc spreadsheet which contains two sets of time series data e.g.
|Date-A |A | | | |Date-B |B |
|2020-01-01|101| | | |2020-01-01|201|
|2020-01-02|102| | | |2020-01-02|202|
...

**1**

vote

**2**answers

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

**0**

votes

**1**answer

45 views

### i want to return value( eg;- sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps

see this screenshot for reference
see this screenshot..
I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL
and i will assign the TOTAL ...

**0**

votes

**1**answer

308 views

### Hyperlink to certain text in excel page?

I'm trying to create hyperlinks to certain points in a large data sheet. Each are separated by a block of text identifying what is there. I know I could create the hyperlink to certain cells in the ...

**0**

votes

**1**answer

25 views

### How to copy rows of specific columns based on a criteria (and then continuously update)?

I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...

**0**

votes

**1**answer

26 views

### In LibreOffice Calc, why would sort buttons and menu items suddenly become disabled?

I was creating a spreadsheet in LibreOffice Calc. Several times, I used the "Sort Ascending" and "Sort Descending" toolbar buttons. All was well, and everything was working as ...

**0**

votes

**1**answer

32 views

### How to get LibreOffice Calc to recognize dates in YYYY/MM/DD format?

I've been given a large tab-separated-values (TSV) text file that contains dates in the format YYYY/MM/DD.
I tried copy and pasting the dates into LibreOffice Calc, but it didn't recognize the strings ...

**1**

vote

**1**answer

35 views

### How to calculate max sum held over a month?

I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...

**1**

vote

**2**answers

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

**0**answers

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

**0**

votes

**1**answer

45 views

### Based on cell formula, display another cell value

I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet?
Column A on sheet 1 has a size and column B on sheet 1 has a tolerance.
Could be same ...

**0**

votes

**3**answers

116 views

### Excel MAX IF Function To Ingore Zero Date (1/0/00)

=MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967)))
I want to ignore Zero date and result should give me 0 or blank instead ...

**1**

vote

**1**answer

41 views

### Sum and lookup over range in spreadsheet

I have a google spreadsheet that looks like this
Items
Item
Value
A
10
B
12
C
15
D
20
Calc
Total
Items
A
A
B
A
A
C
C
C
In Total I want to have the a formula that sums the value of the ...

**0**

votes

**3**answers

50 views

### Distinguish between A* and A in excel

I’m creating a spreadsheet to be used in schools that use various countifs, subtotals etc to look up data. A big problem I have is that I need to distinguish and count separately students on an A* (A ...

**0**

votes

**3**answers

63 views

### Is there a way to define a range with infinite row or column number in a spreadsheet program?

When defining a named range in a spreadsheet program, is there a way to define a range that covers for example A1:A[*infinity*], so I will be sure that the range will always include all rows, whether ...

**0**

votes

**1**answer

41 views

### How to group multiple date|data columns to one single date column on the left?

I've got a large set of data that looks like:
and I've been trying to format it so that instead of having a column of dates for each item, there's only one column of dates on the left. The problem is ...

**0**

votes

**0**answers

29 views

### How to isolate list of values in Excel spreadsheet

I have a randomly generated list of 5,000 filenames. I'd like to isolate this list from a seperate excel spreadsheet of 60,000 rows, in order to extract the respective values from other columns.
How ...

**0**

votes

**0**answers

12 views

### How to return the sheet name of a given cell of a query result

I have scrubbed the internet for answer to this and and either I am asking the question the wrong way or it isn't possible.
Here is a example template of what I am wondering about:
https://docs.google....

**0**

votes

**1**answer

15 views

### Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day...
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | ...

**3**

votes

**1**answer

52 views

### Filtering over merged downwards cells in MS Excel-2016

Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...

**0**

votes

**1**answer

31 views

### How to highlight down columns for many columns at once that are separated in an excel sheet?

I have 30 columns of data with over 10,000 rows. I'm using Ctrl+Click to highlight specific cells and then use Ctrl+Shift+Down arrow to highlight to the bottom, but I noticed it won't let me do that ...

**0**

votes

**0**answers

26 views

### In a LibreOffice Calc (or OpenOffice Calc) spreadsheet, how can you maintain multiple groups of columnar data?

I have a LibreOffice Calc spreadsheet that contains 3 sets of data. It is organized like this:
SUBJECT A SUBJECT B SUBJECT C
Date X Y Date X Y Date X Y
Date X Y ...

**0**

votes

**0**answers

24 views

### Combining multiple data entries, for some unique and some duplicate customers. (100K Rows, 30 Columns)

(Top Table)-I need to remove the duplicate customers (left column) while filtering/move/pasting all of the other data in the columns to the right to move upward to complete it across 30 columns.- ...

**0**

votes

**3**answers

53 views

### Sum of vlookups for each value in a row

There should be a way to do this, and yet I'm struggling...
==The Data==
Sheet1
A B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod | $9....

**0**

votes

**1**answer

26 views

### In LibreOffice Calc, is it possible to use a complex function and a data range as arguments for a MIN() function?

I'm going to simplify a bit to hopefully get at the crux of my question.
I have a spreadsheet in which a cell has a massive formula. (Again, I'm simplifying to avoid a question that is so long no one ...

**0**

votes

**1**answer

68 views

### Too Many Arguments when using IF google sheet

I want to implement this formula in Google sheet and I'm getting the error for too many ifs. Any help?
=IF(I2="win",SUM(J2*G2),IF(I2="loss",SUM(J2*-1),IF(I2="push",J2),IF(...

**0**

votes

**1**answer

79 views

### Libreoffice Calc : Fill in a range of cells with a known start and end value. Automatically

I have two cells separated by some empty cells. Values in parentheses.
A1 [ 23 ]
A23 [ 433 ]
I want the spreadsheet to figure out the best way to increment the values between them, based on how many ...

**0**

votes

**2**answers

22 views

### Making formula work only when adding to a value

I am trying to have a number automatically update itself on our inventory spreadsheet (since we seem to forget to do so ourselves)...but I can't seem to get it quite right.
So our inventory process ...

**0**

votes

**1**answer

87 views

### What is the processed called when converting data in repeated columns to rows on a spreadsheet?

If I have a spreadsheet with a data structure where similar information is repeated across multiple columns like so:
TxID Date Merchant Item 1 Name Item 1 Value Item 2 Name Item 2 ...

**0**

votes

**0**answers

25 views

### How do I keep just the first row of each unique occurence of a value in a column in LibreOffice Calc?

I have a sheet in Calc that looks like this:
A B C D E
1 Call # Title Barcode Status Check-out date
2 070.49 GUIB The ...

**0**

votes

**1**answer

42 views

### Excel Change value of each cell with a value in row to count+1 of cells with values in row

I have the following spreadsheet:
I have 57 rows and ~750 columns, in some cells I will have the number one. I want to change the cells with one to a rolling count for each row. I have shown what I ...

**0**

votes

**1**answer

54 views

### How to fix a messed Excel sheet and make a proper format?

I have a problem in a specific sheet: I need only country,network,NetworkName and currency columns, but in sheet all messed up. As you can see in network Colum there are name and MCCMNC code also same ...

**0**

votes

**0**answers

28 views

### Sorting an excel column with numbers and keep track of cell indices

So, this is my problem, I have two excel columns like :
1 // 0.5
2 // 3.3
3 // 1.2
I would like to sort the second column in ascending(or descending) order, and also change the first column as ...

**2**

votes

**1**answer

48 views

### How to get the total sum of counted values separated by commas in multiple rows, but disregarding zeroes?

I want to count the number of values in multiple rows separated by commas--excluding those that have 0 values, using the following formula:
=SUM((LEN(SUBSTITUTE(C30:H30&",",",0"...

**0**

votes

**0**answers

43 views

### Fill down every (x) rows for (x) rows inserting as new rows instead of overwriting what's below in spreadsheet (WPS Office)

I have an existing CSV which contains shipping rates for every country in the world.
I need to extend the rates for each country. Each country currently occupies 80 rows each.
Every 80th row (the last ...

**2**

votes

**1**answer

62 views

### Merging different number of rows with some common data

I have a data like this in an excel sheet:
Rensi 06/10/2020
Rensi 07/10/2020
Rensi 08/10/2020
Sam 06/10/2020
Sam 08/10/2020
Mathew 07/10/2020
I want to ...

**0**

votes

**1**answer

637 views

### Excel: How to show a floating text box only when I select the cell?

There's a type of text box in an Excel file that is associated with a cell
and appears only when you select that cell uniquely
(e.g., by clicking on it or moving to it with cursor keys).
Hovering ...

**0**

votes

**3**answers

219 views

### Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells

Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells or does not find the lookup value.
I do not want to suppress the zeros by formating them out. I want ...

**0**

votes

**1**answer

41 views

### How do I assign a name to a value in excel?

How do I assign a value to a name?
For example If the word "WHOLE" is in Column I it will count as "1", and if the word "HALF" is in column I that it will count as "....

**0**

votes

**0**answers

22 views

### How to get a conditional scale with with 4+ colours without VBA?

How do I achieve a conditional scale with with 4+ colours without VBA?
This is what I am trying to achieve:
The only way I know to achieve the above is by using two separate scales which is very ...

**0**

votes

**1**answer

29 views

### XLS: 'if statement' calculation

I have a cell B19 which consists of taking the value of another cell and dividing it by 5:
=B15/5
However I'd like to change it so that it only divides it by 5 if the B15 is more than 1000... and for ...

**0**

votes

**2**answers

30 views

### Function that writes name depending on two conditions

I would like to know how to implement this in an excel formula: I have a column with some names, a row with different numbers (dates), and the remaining values work as "keys". For instance:
...

**0**

votes

**1**answer

77 views

### Why does conditional formating “white out” only certain manual formating is some cells?

I'm trying to make a spreadsheet with tables, with some automation, that keeps track of my work hours. (Note I'm not using the Excel "format as table" feature. This is all by hand). ...

**1**

vote

**1**answer

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

**0**

votes

**2**answers

55 views

### How to write an Excel Formula that takes a total across multiple repeating rows [duplicate]

I'm working with payroll data in a spreadsheet. What I'm looking to find is a total across different rows of employees with different roles. For example, how could I write a forumla for the "...

**0**

votes

**1**answer

45 views

### Sumifs function in excel

Hi my SUMIFS function is not adding up figures at all. The SUMIFS formula I use is below:
=SUMIFS($O:$O,$A:$A,"distribution - reinv",$D:$D,">="&DATE(1,5,2020),$D:$D,"&...

**0**

votes

**1**answer

41 views

### Excel: how to sort rows by same column values

I want to sort (keep) rows by the same column values. For example,
As you can see above, I want to sort the rows by the same (matched) column values of code1 and code2 as follows:
Basically, I have ...

**0**

votes

**1**answer

28 views

### Excel: Selective duplicates removal

I want to remove some duplicates, but not all. Let me give you an example below:
A B
UK James
UK Coco
UK Lydia
UK Matthew
US Max
US Kim
US Anna
FR Marie
FR Lin
I want to turn the columns ...