Questions tagged [spreadsheet]
Questions about spreadsheet software in general and interoperability between spreadsheet programs.
697
questions
0
votes
2
answers
66
views
Why some cells can not be sorted in Excel
I am met with very strange things with spreadsheets in Excel now. Some cells with a date like 2023-10-22 can not be sorted with other cells in the same format in a column. I changed the format of the ...
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
0
votes
1
answer
14
views
How to formulate reference another sheets if i have list of sheets name in sheets1? On sheets
How to formulate reference another sheets if i have list of sheets name in sheets1?
I want the value in cell b2 in each sheets.
0
votes
0
answers
23
views
Find a chart by his title in Excel spreadsheet
I have an Excel document with numerous charts on one of its sheets. I need to locate a specific chart by its title. Is there a method, similar to using the search and replace tool, to easily find the ...
1
vote
1
answer
66
views
Coloring cells across multiple excel sheets in an xls file based on a condition
I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers.
(Note that ...
0
votes
2
answers
84
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
17
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
234
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
167
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
823
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
47
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
90
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
115
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
109
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
219
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
535
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
50
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
139
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
455
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
748
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
75
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
38
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
222
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
2k
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
112
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
149
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
167
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
93
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
51
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
148
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
647
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 (...
1
vote
1
answer
65
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
43
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
68
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
68
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
37
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
80
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
46
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
7k
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
36
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
438
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
524
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
401
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
576
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
60
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
455
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
451
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, ...