# Questions tagged [spreadsheet]

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

693
questions

0
votes

2
answers

64
views

### How to shorten Excel OR function for many conditions?

As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF.
My formula: =if(or(b3/b$2<0.4,c3/c$...

0
votes

1
answer

14
views

### Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text

A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...

0
votes

2
answers

64
views

### How do I create a formula that will count all the "x" in a list, then give me a total for amount paid based on amount of "x"?

I have a list aside for a staff party where we have a running total of amount paid at the bottom, and are indicating who is attending by placing an X next to their name. I would like it to be so that ...

0
votes

2
answers

54
views

### Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates

I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)
Sheet1
Sheet2
In other words, I ...

0
votes

1
answer

181
views

### How can I evenly auto-distribute values across 12 columns in a spreadsheet so that their sums are as close to equal as possible?

I'm trying to distribute a bunch of annual recurring business costs across the span of 12 months so as to make each month's total expenditure as close to each other as possible. Doing this manually is ...

0
votes

1
answer

28
views

### Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?

I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...

1
vote

1
answer

64
views

### replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet

I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...

0
votes

1
answer

68
views

### Can I have excel automatically swap text in a column from one word to another without using another column?

I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...

0
votes

3
answers

91
views

### Return First Column Header of Instance of String

I have a collection of speakers who have spoken various dates.
Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018
1st-Speaker,Jim,John,Fred,Sam,Scott,Riley
2nd-Speaker,...

1
vote

1
answer

91
views

### How can I stop libre office sheets jumping when scrolling wide columns?

Long time sufferer of Windows, moved to Linux Mint around a month ago and I am still getting used to the changes. One very annoying thing that I have Googled, looked at the questions here marked libre ...

0
votes

0
answers

299
views

### Copy output from Google Colaboratory and Paste in Excel

Given a Colab notebook with the following cell
url = 'https://files.grouplens.org/datasets/movielens/ml-latest-small.zip'
extract_zip(download_url(url, '.'), '.')
movies_path = './ml-latest-small/...

0
votes

1
answer

38
views

### Find stdev of values in col B where col A matches criteria

I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days.
I am able to do sums with a sumifs which works on ...

0
votes

1
answer

99
views

### Can I use Excel to solve a quadratic based on a table of x and y values? (without using a graph)

I have the following data sets where the top bold row are my y-values and the following rows (%) are my x-values. Each row is a different set of x-values. I'd like to plug in 80% for x for each set ...

2
votes

1
answer

138
views

### How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)

I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...

1
vote

1
answer

290
views

### Turn cell references in Excel hyperlinks into the text in the referenced cells

I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...

0
votes

2
answers

43
views

### Excel opens at top of sheet only

How can I get Excel (using Office 365) to open where I left off (usually the bottom) and not the top of the sheet? I have a large spreadsheet and usually need to add new information at the bottom. ...

0
votes

0
answers

35
views

### How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?

How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?
Here is an example: on https://www.amazon.co.uk/s?rh=n%3A20606777031&...

2
votes

1
answer

80
views

### Spreadsheet: View the entire (long) text of selected cell

I am not using spreadsheets very often and I am not sure whether this is a feature of common software (I'm using libreoffice calc).
I have a spreadsheet in which 50 to 200 word texts are entered in ...

2
votes

1
answer

1k
views

### convert excel spreadsheet with format to markdown

I want to convert a spreadsheet table with basic formatting (bold, script) to MD (markdown) format.
I know howto convert a table to Markdown but it losses the basic format and i need to preserve it.
...

0
votes

1
answer

63
views

### Sequential numbering of codes

I have a sheet where as I enter details of a product, an internal 4 character code is generated...typically 4 letters (e.g. ABCD or BADC). This is just based on simple VLOOKUP and CONCAT functions. ...

1
vote

1
answer

68
views

### reference range in different sheet with Indirect column and row

I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this:
=INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE)
instead of
=dataset!a1:...

4
votes

1
answer

107
views

### How to stop Excel from combining Conditional Formatting rules into 1 broken rule

I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it.
However, I'm unable to grasp how painfully stupid ...

1
vote

1
answer

78
views

### What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?

Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures?
The question might be similar to these ones but the ...

0
votes

1
answer

42
views

### Concatenate data (matrices) in separate sheets (Gnumeric)

I need to "concatenate" data from two sheets into a third sheet. I need to do this in Gnumeric, but presumably it should be doable with other spreadsheet programs as well.
Sheet 1:
| A ...

1
vote

1
answer

65
views

### Drag to fill duplicates values

I want to create a sequence of dates that populate a column.
A
2023-12-31
2023-12-30
2023-12-29
...
Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and ...

0
votes

1
answer

79
views

### Find next cell within column that is a number, starting from reference cell in another sheet

I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...

0
votes

0
answers

12
views

### Spreadsheet Formula

I have a spreadsheet. I want to create separate spreadsheets for each of the person listed on the column C that pulls new rows from the Master Sheet, only when their name appears in column C. So the ...

1
vote

1
answer

53
views

### Custom Information Message for user based on another cell value

I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...

0
votes

0
answers

16
views

### Dynamically changing content of a cell dropdown for validation because of a huge list

I have a huge lookup list of items, each with a conversion factor that I wish to look up.
The lookup list is too big to be practical for a validation dropdown - this is how it looks:
Here's the ...

-1
votes

1
answer

40
views

### Spreadsheet: store multiple entries for one "field" per row

I have a spreadsheet that inventories our sheet music collection with multiple fields like title, composer, etc:
Octavo #
Title - original language
Composer
Arranger
Editor
Poet
Translator
Voicing
...

0
votes

1
answer

50
views

### Print Excel Spreadsheet using Template?

So Let's say I have a spreadsheet
No
Name
Class
Gender
1
Jack
Science
Male
2
Rose
Science
Female
3
Kevin
Social
Male
4
Olive
Social
Female
and I have template
No
Name
Gender
1
2
3
4
How to ...

1
vote

0
answers

62
views

### How to draw empty cell values in LibreOffice Calc

my problem is the following (didn't find info how to do it in the official documentation) - recorded ~108k ping results to LibreOffice table with columns
DATE (letter A),
LATENCY (letter B),
STATUS (...

0
votes

1
answer

36
views

### Microsoft excel formula to connect codes of smaller areas with those of the bigger ones and sum up their values

I am working with geographic information and I have bigger areas called Regso that concist of several smaller areas called Deso. The data I have is for the smaller Deso areas but I would like to ...

0
votes

1
answer

28
views

### Check row for a specific string and get the value of a column cell where the initial parameter was located

I am trying to create a calendar checker that checks if a value is detected on a specific date and return the month it was detected at:
In the image I am looking for the string "FN" ...

0
votes

0
answers

44
views

### How can I combine two Excel formulas into one?

In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade.
Now, I need to combine two formulas so that when the four ...

8
votes

4
answers

5k
views

### Formula to see if a series of numbers is increasing in value?

I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...

0
votes

1
answer

33
views

### Excel - The most spending occurred around this date

I feel like as a human I can understand this, but I can't figure out how to get it into an Excel formula.
Given the dates and amounts spent on those dates below, how can I find a date on which the ...

0
votes

1
answer

35
views

### How can I copy a larger number of cells into a smaller number of cells that are already feeding a graph?

I have two 10-cell columns that currently feed a graph (one for the x-axis, the other for the y-axis). I have now doubled the discretization of my dataset, so instead of the original 10 data points, I ...

-1
votes

1
answer

263
views

### Excel: How to count how many times a consecutive text value appears

I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.
I got this spreadsheet where I make those schedules but ...

-1
votes

3
answers

1k
views

### Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?

I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.
Instead of transposing every nth value, my ...

1
vote

2
answers

286
views

### How can I find which cells in a column (or at least the first one) are greater than a threshold in a LibreOffice spreadsheet.?

I have a LibreOffice spreadsheet (actually a CSV file open in spreadsheet).
It has several columns.
Something like this:
1,3,4
2,4,6
1,3,4
10,3,4
2,4,5
0,3,4
-10,3,4
I want to find in a column (say ...

0
votes

1
answer

307
views

### How to highlight all cells that contain formulas

In LibreOffice Calc, I want to be able to quickly distinguish between cells that contain input data and formulas. Enabling "View → Value Highlighting" changes the font color in cells such ...

0
votes

1
answer

318
views

### Automating a repeating pattern across multiple worksheets

TL;DR: I need to automate a cyclical pattern that repeats over intervals that span blocks of cells across multiple sheets.
So... I have a spreadsheet that I maintain for our work group that we use for ...

0
votes

2
answers

39
views

### Automation between a google spreadsheet and the downloaded version. Possible?

I use an iMac and google spreadsheets.
When I want to synchronise my spreadsheets on my drive with the spreadsheets on my imac, I tend to download them. The problem I have is the downloaded ...

3
votes

2
answers

354
views

### How can I convert time values with a letter suffix like (30m, 2h, 30s) into an actual time format?

I have decimal values followed by a letter to indicate the time unit (hours/minutes) values like the following:
60m
1.5h
15m
20h
How can I convert these to a numeric value of minutes or time value? ...

2
votes

3
answers

307
views

### Parse dates like "20220928" in Excel

I have an input column which contains an id + datetime:
id12345!20220926_100911
My goal is to get the date and I have already successfully extracted the date into text with a formula involving LEFT, ...

1
vote

2
answers

79
views

### More advanced filtering with text in Excel

I would like to filter columns in Excel with use of Text Filters based on multiple criteria in text search. Is it possible?
]
]
how I would like to filter it based on words "medium" and &...

0
votes

1
answer

29
views

### Adding column description of one spreadsheet sheet in another spreadsheet of same excel

I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet.
Example:
Suppose I have sheet 1 with 6 headers for 6 columns, ...

1
vote

0
answers

72
views

### Creating a auto-populated population spreadsheet file in Google Sheets

Not sure if this is the proper place to post this, but worth worth a try. I am using Google Sheets to create a series of files for worldbuilding for a bunch of stories. What I am trying to do is have ...

1
vote

3
answers

343
views

### Copy cells in spreadsheet by entering coordinates

I have a large spreadsheet with 15346 rows
I enter a formula in B2, and want to copy it to B3:B15346. (I don't want a copy of the value in B2, I want an adjusted formula in each cell)
Is there a way ...