Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1,823
questions with no upvoted or accepted answers
2
votes
0
answers
27
views
Google Sheets: shared 'Make a Copy' link but still requesting access to master copy
I have been sharing spreadsheet templates on Google Sheets via links which instruct the users to make a copy, so they are able to fully access and edit their own version (by changing 'edit' to 'copy' ...
- 21
2
votes
0
answers
139
views
How to count cells and blank cells after up until another cell in separate columns
The title is a bit confusing, but essentially I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should count those values and blank cells ...
- 21
2
votes
1
answer
17
views
Google sheet trend chart help
Is there a way to display this years data like it is in this chart but have a cut off so the line doesn't drop to 0?
Please see attached image
Many thanks
- 31
2
votes
1
answer
42
views
How to assign cell values based on options from another list
I am sure there is a simple formula for this but I have not been able to find a solution anywhere.
I am trying to fill a cell based on a list in a different tab.
In my main sheet, each row has a ...
- 21
2
votes
1
answer
276
views
Filter date, and add blank rows between different time slots
I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day.
Here is a ...
- 21
2
votes
1
answer
56
views
How to import a JSON-style table [['a', 'b'], ['c', 'd']] into Google Sheets?
Let's say I have copied a JSON-style string like this:
[['a', 'what\'s up'], ['c', 'd']]
into the clipboard (for example copied from my browser Dev Console, after some JS code).
How can I easily ...
- 546
2
votes
0
answers
2k
views
Modifying the source document for linked tables in Google Slides
I have Google Slides presentations I use as report templates with data in charts and tables linked to a Google Sheets document. In some cases I need to change the document that is the source for the ...
- 441
2
votes
3
answers
134
views
Google sheets random number generation help
I'm trying to generate random numbers in an interval, x number of times.
Imagine rpg dice sintax, like:
3d6 means roll 3 times a dice of 6 sides. Or 5d100 means roll 5 dices of 100 sides.
If I was to ...
- 21
2
votes
1
answer
137
views
How do I filter cells by a list of keywords, and import them into another sheet in a workbook?
I've got a list of customer feedback from a company's website. We'd like to filter the data by store, so we can shout out the staff members or stores that are doing particularly well, and give them ...
- 21
2
votes
1
answer
84
views
Round up duration calculation to the next multiple of five minutes
I currently have this formula:
=text(D2 - C2, "hh:mm:ss")
I need this formula to round up to the nearest 5 minutes.
I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
2
votes
0
answers
118
views
If there anyway i can check if the cell contains the result of a arrayformula()?
For example i have this in A1 :
=arrayformula(B1:B100)
If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
- 315
2
votes
1
answer
245
views
Weighted Average Grade Book Based on Conditional Dropdown
I am trying to create a grade book using Google Sheets that produces a weighted average based on three grade categories; exams=45%, participation=30%, and homework=25%. I would like to do this be ...
2
votes
1
answer
25
views
Compare imported range with existing range, and filling corresponded data to the fields
I am not sure how to import data to corresponding cells.
query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
- 21
2
votes
1
answer
537
views
How to combine formulatext() and arrayformula()?
I want to use formulatext() with arrayformula() :
=arrayformula(formulatext(A1:A10))
But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output.
Is there ...
- 315
2
votes
2
answers
1k
views
Delete rows in Google Sheet after 30 days
I have a Google Form that populates a Google Sheet with rider registrations for a local cycling club. The sheet has a filter in place to display only the riders for the current day. This filter ...
2
votes
1
answer
380
views
Apply custom formula across all columns using Array Formula
I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
- 21
2
votes
1
answer
1k
views
3D Formula Google Sheets - sum across sheets
I'm trying to script an equivalent to Excel 3D formula into Google sheets.
I have a workbook with numerous worksheets and a master sheet that should sum up the data.
In excel I would have used this 3D ...
- 21
2
votes
1
answer
56
views
FILTER between columns
I have thousands of data in 2 columns
First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
2
votes
1
answer
33
views
Function to create a calendar for one year with gaps (date and days in separate cells)
Is it possible to have a function within a Google Docs spreadsheet which I can link to a button which will create a calendar in the following format:
Number will be 1 - 365 for the days
Date will be ...
- 21
2
votes
2
answers
3k
views
Select Unique on 1 Column in Google Sheets - Distinct Doesn't
I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
- 21
2
votes
1
answer
74
views
Check if a string appears within another cell, and return the longer of the 2
I am having a hard time thinking of an formula that can help me pull the data needed in this example:
In the C1 cell, I would like to make a formula that does this:
Check if any cells in the A ...
- 21
2
votes
1
answer
184
views
Replace first value in a given range (column)
How would I look up the first value < 0 in this range and replace it with 0? If the value <0 does not exist, I'd like the column to remain intact. I've been struggling with this for a day now
...
- 21
2
votes
1
answer
288
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 | false
...
- 133
2
votes
1
answer
54
views
Add '/' character in between a number (which should be formatted as a date) and turn it to a date
I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
- 21
2
votes
1
answer
28
views
How do I write a formula while referencing another sheet in Google Sheets?
So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line?
=SUM(Sheet!((COUNTA(A2:A516)/...
- 65
2
votes
1
answer
37
views
Getting the slope of the 10 cells above current
I'd like a formula for Google Sheets, but I'm having trouble figuring it out.
Say I'm on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add ...
- 21
2
votes
2
answers
112
views
Spreadsheet formula to calculate most common items in ordered items lists
I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items.
Like is you have an Oscar ceremony and you get votes from ...
- 163
2
votes
1
answer
111
views
Find and replace Google Script
I've read a lot of questions and I tried several Script for Google Sheets but nothing works for a very simple case:
I would love to replace all the points "." with the commas "," ...
2
votes
2
answers
644
views
A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell
Is it possible for:
a set of checkboxes to recognise lists (grouped in one cell) on entry (via formula)
to then have the same checkboxes copy the list to another cell as it returns "TRUE"
...
- 33
2
votes
0
answers
654
views
Sheets - display ISO8601 string as localized date
In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string?
The use case is that I have ...
- 121
2
votes
1
answer
191
views
Aggregate data to show text in one cell
I need to put together multiple entries into one where the results is text and has to be in one cell.
From this
to this
- 21
2
votes
1
answer
481
views
How do I copy formatted formula results in Google Sheets?
I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I ...
- 243
2
votes
0
answers
22
views
Customize point style in scatter chart [Google Sheets]
Is there a way to add new point shapes (import new symbols) in Google Sheets charts? Also, can I customize there color fills, or remove the infill color?
- 121
2
votes
1
answer
646
views
Average the last N values in a Sheets column using Arrayformula
Experts, I need your help...
I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't.
Let me ...
- 21
2
votes
1
answer
231
views
Matrix multiplication-esque concatenation?
I'm making a simple recipe maker in google docs, using row vectors and sumproduct I can get it to calculate the protein and calories for a given recipe. The way it's set up it can be hard to read the ...
- 21
2
votes
1
answer
207
views
Trying to pull Duplicate data from multiple columns
https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0
I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
- 21
2
votes
0
answers
357
views
How to Group Set of Rows & Skip One Every Time
I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5.
Right now, I have as script that is ...
- 120
2
votes
1
answer
429
views
ImportHTML Altering Data Format/URL Fetch Issue
4/17 6PM Update *** I am now trying to use the URL Fetch App. I've been playing with this for hours, but can't seem to figure out the issue. I am just trying to get the info from this site as plain ...
- 120
2
votes
0
answers
1k
views
Get page number and page total (eg. Page 1 of 4) into the footer of a google sheet when exporting as PDF
In Google Drive Sheets you can use the custom header and footer tool to add custom headers and footers when you export a spreadsheet as a PDF.
I would really like to get into the footer a statement ...
- 6,833
2
votes
1
answer
481
views
How do I copy data from multiple Google sheets and paste into master Google sheet while maintaining format?
Each day I paste assignments to employees in each employees respective sheet. These assignments have hyperlinks in portions of the data. At the end of each day I copy their assignments for that day ...
2
votes
0
answers
187
views
Create a pivot table with a column for each assignee, containing rows of task descriptions
I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence.
Crude representation:
I'd like to be able to ...
2
votes
2
answers
2k
views
Conditionally format if date is same/greater or smaller than current date
I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell.
Example: today is 21/02/2020.
The date in the cell is 25/...
- 21
2
votes
1
answer
111
views
How to make a cell autopopulate the date dependent on another cell
I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
- 43
2
votes
0
answers
53
views
How to use COUNTIF and UNIQUE
I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
- 21
2
votes
1
answer
3k
views
google sheets checkbox conditional formatting
I have a schedule where I use inspector name, job number and then checkboxes to book the inspector on the visit date
What I'm looking to do is if an inspector is booked in a job on lets say jan 3rd ...
2
votes
0
answers
1k
views
Google Sheets: Filter Row if it does not contains HYPERLINK
I have a sheet which has a column where most cells contain a hyperlink, I am looking for a way to find cells which do not contain a hyperlink and delete those rows.
Filter by condition -> Text ...
- 21
2
votes
1
answer
1k
views
Auto sorting by multiple columns in Google Sheets
I have a data-set which I would like to have sorted with first column 6 and then column 7.
From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
2
votes
2
answers
691
views
Conditional Formatting in Google Sheets
The shorter question to spare you from reading more than you need to
I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
- 129
2
votes
0
answers
110
views
Custom number formats in Google Sheets: Issue displaying negative numbers
Context: Looking to display large numbers used in financial reporting & analysis in an abbreviated easier-to-read format.
I currently have the GS custom number format as follows:
[>=999950]#,...
- 121
2
votes
1
answer
365
views
Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets
Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?
The spreadsheet I'm trying to do this on ...
- 31