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,822
questions with no upvoted or accepted answers
2
votes
1
answer
1k
views
Importing data into Google Form response sheet with date, list, grid
I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses ...
- 21
2
votes
1
answer
410
views
In google sheets, want to copy cell values from 1 sheet to another if a specific cell has any text
I have this working well:
=if('Lee''s sheet'!$AP2="x",'Lee''s sheet'!B2,"")
which will populate cell values into a cell, from 1 sheet to another if there is an "X" marked in column AP.
I want to ...
2
votes
0
answers
1k
views
How can I create a violin plot in Google Spreadsheets?
How can I create a violin plot in Google Spreadsheets?
Example of violin plot:
- 17.3k
2
votes
1
answer
179
views
How to generate standard competition ranks (not dense) for my Google Sheet?
What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
- 21
2
votes
0
answers
81
views
IMPORTRANGE, where range shifts often, to different spreadsheet
What I have: a client information spreadsheet that contains company information including their last order date following the status of their account (active/follow-up).
What I need: the last order ...
- 21
2
votes
1
answer
481
views
Google Spreadsheet sum when filtering
I have spreadsheet that looks like this:
Now when I filter on a company I want the total to display the sum of the whole of B column.
Since I am very new to spreadsheets I was hoping someone could ...
- 121
2
votes
1
answer
99
views
Reference formulas from one sheet into multiple sheets
I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced.
For example, if I have =...
- 21
2
votes
0
answers
83
views
Google Sheets: move a chart and still have it update from original data/arrays?
Can you move or copy a chart in Google Sheets into another EXISTING sheet and have it still update form the original data arrays?
I know you can move a chart into its own sheet, but I'm looking to ...
- 21
2
votes
2
answers
18k
views
How can you disable "Show Edit History" for a single cell?
I turned on Show Edit History for one cell in a Google Sheet and now I want to turn it off for that one cell.
I can't see any way to do so. If I right-click on the cell I see Show Edit History but it ...
- 41
2
votes
1
answer
2k
views
Writing a macro to create a new tab and name that tab based on a cell value in Google Sheets
I would like to have a Macro that generates a new tab and names that tab based on a cell value.
There is a tab called 'Dashboard'! on which the user enters information in assigned cells. Afterwards, ...
- 21
2
votes
2
answers
881
views
Google Sheets formula for unique values across columns
I'm familiar with the UNIQUE function in Google Sheets and how it works on rows (see Examples 1 & 2 below).
Is there a function in Google Sheets that operates similarly on columns?
In Example 3 ...
- 180
2
votes
1
answer
828
views
Google Sheets countif attendance sheet, quarterly report
I have a Google Sheet that I use to track attendance at an open lab that I run. Each quarter I put together a report where I count the number of people that attended that quarter. If I just use the ...
2
votes
1
answer
371
views
When I insert a row, indirect does not update
I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
2
votes
1
answer
37
views
Adding mm:ss times when expressed as TEXT
I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets.
The numbers are supposed to be minute second. mm:ss. For the ...
2
votes
2
answers
172
views
How to make sequential list of months?
I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
- 246
2
votes
1
answer
522
views
Google Form response is modifying a cell on another row of linked sheet
I have a sheet that is recording form responses on a linked Google sheet. The issue is that sometimes the form will modify a cell on the row above in addition to the row that is being inserted (see ...
2
votes
1
answer
516
views
SUMPRODUCT inside ARRAYFORMULA in Google Sheets
I have a SUMPRODUCT equation that works fine on one row:
=SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10))
However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically ...
- 21
2
votes
1
answer
8k
views
Change default zoom on a Google Sheet?
I use a shared Google sheet to display apparatus issues for a fire department. This is displayed on a tv using an Asus Chromebit. Every time someone logs into the sheet it resets the zoom to 100% ...
- 21
2
votes
2
answers
445
views
How do I get the count number of weekdays in a datetime column in Google Sheets?
I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday?
This gives me a ...
- 85
2
votes
1
answer
385
views
Google Forms sending me duplicate email on automatical email
So, I've used this code, just reworked for my needs:
Can Google Forms send emails automatically?
The problem I have is that the account that sends those emails also receives them. Is there a way to ...
- 21
2
votes
1
answer
1k
views
Run a Script When Tab Programmatically added to a Google Spreadsheet
I'm trying to pull data from an Adwords report to a Google Sheet through an Adwords script. This will be done on a daily basis. Every time the Adwords script runs, a new tab is created (and an old one ...
- 21
2
votes
2
answers
1k
views
Jump to today's date when opened
I have found a similar question here:
How to make Google Sheet jump to today's column when opened?
I want to do a similar thing, but I have the dates in row 5 not in the column. I tried several ...
2
votes
1
answer
47
views
LOOKUP for data from other sheet
So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail.
So what I'd like to do is, ...
- 21
2
votes
1
answer
296
views
How to join VLOOKUP results of multiple sheets into one cell
I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it.
In this example, I am ...
2
votes
2
answers
691
views
Next Payment Date
I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
- 21
2
votes
1
answer
209
views
Restrict entry to a group of cells based on the value in another cell
I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
2
votes
3
answers
3k
views
Select last entry from spreedsheet
So I have a Google Form dumping data into spreadsheet tab form responses 1.
I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
2
votes
0
answers
3k
views
Change tab color based on cell value that is a formula OR cell background color
1st time question asker, so I'm apologizing in advance if my question is worded wrong or doesn't make sense or is, frankly, to wordy (I tend to over explain).
I have a sheet with, currently, 10 tabs. ...
- 21
2
votes
2
answers
48
views
Using multiple IF and OR statements with calculations
I'm trying to calculate unit price based on quantity.
I have nested IF functions to check the quantity and multiply that by the correct unit price.
Unit Price Table
QTY >=
QTY <=
Unit $
0
...
- 21
2
votes
2
answers
402
views
How do I prevent or omit a negative value when calculating between times?
I've researched through the site to see if this has been answered but with no luck after reading:
How do I calculate a time delta in my Google Spreadsheets?
Calculating time difference
Calculating ...
- 153
2
votes
1
answer
682
views
Sum upwards until another column's value is met
I found a handful solutions for summing down, but nothing for summing up.
Example table:
type | value
A | -
B | 5
C | 10
D | 7
A | [SUM1 = 7+10+5 = 22]
B | 4
B | 2
C | 3
C ...
- 1,647
2
votes
1
answer
854
views
Add a new row to a spreadsheet, copying down formulas when a certain cell reaches a particular value
I've got a simple Google Sheet (it's literally one line), that has in A2 =now() to calculate the current time, and then a series of formulas calculating various dates/times based on the current date ...
2
votes
2
answers
791
views
How to count the number of empty rows since the last entry in a column?
I'm trying to get Google Sheets to tell me how many rows it has been since the last "1" was present in column A (see example table below). How do I get Google Sheets to output column B?
An ...
- 21
2
votes
1
answer
1k
views
Temporarily disable autosave in Google Sheets
I use autohotkey scripts for a lot of things in Google Sheets. It's basically scripted, automated keyboard input. A script may look something like this:
Loop, 4
{
Loop, 7
{
Loop, %...
- 121
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
2
votes
0
answers
520
views
How to align one axis in a two axis chart
I want to align my right vertical scale with my left vertical scale so both have the 0 at the same height.
I have this:
And I want this (I've done it manually but it's not perfect, look how both 0s ...
- 21
2
votes
1
answer
2k
views
Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?
I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
- 489
2
votes
1
answer
4k
views
Update Cell If another cell is not empty
There are two columns N and O.
When any cell in column N is filled the adjoining cell in column O needs to filled with "Yes" if not it stays empty.
This is the script I'm using.
var s = ...
- 145
2
votes
2
answers
63
views
Count how many times a certain word comes before and after another in a list
I am trying to evaluate some information from an animal studying exercise using Google Sheets.
I'd like to know how many times the animal:
forages then allogrooms
sleeps then allogrooms
etc.
Also ...
- 31
2
votes
0
answers
1k
views
My ImportXML with XPath doesn't work
I've got a problem. I'm trying to import data in my google sheet document using =ImportXML with XPath from this page : https://www.boursorama.com/bourse/produits-de-bourse/cours/warrants/...
- 21
2
votes
1
answer
1k
views
How to send Google Form results to a particular sheet ("tab") of a Spreadsheet
Is there a way to create a Form that submits to a particular TAB of a Google Sheet?
Use Case:
We are using this for meeting pre-check-in. There's standard form. I'd like the results for each ...
- 3,051
2
votes
1
answer
166
views
How can I pull a value from one column based on the values of another column?
When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off."
For example, if the "Amount Owed" dropped below zero at "Year" 1,...
2
votes
1
answer
708
views
Linking multiple script created forms to 1 sheet in pre-existing spreadsheet
I work in manufacturing. I have created a Google form for our leadership safety audits which requires unique information (name(s), area being audited, and date) and also includes 25 multiple choice ...
2
votes
1
answer
453
views
Dividing one column by another based on values on other columns and getting the sum
In the following image, what I'm trying to do is to find a formula that will divide values on column C by values on column L, ONLY IF:
Value on column D = "L", and
Value on column J = "Right", and
...
2
votes
1
answer
83
views
Is there a way to group rows by a term and insert this term as a header in Google Sheets
I have a list of books on Google Sheets that will be turned into a paged layout on InDesign. Each book has two additional information besides its title (year and theme).
Like this
But I need this ...
2
votes
2
answers
287
views
Is it possibile to concatenate with bidimensional arrayformula?
I have a table like this:
+----------+----------+----------+----------+
| Photo1-1 | Photo1-2 | Photo1-3 | |
+----------+----------+----------+----------+
| Photo2-1 | Photo2-2 | | ...
- 53
2
votes
2
answers
380
views
How to search in Gmail from a link of an email address in Google Sheet?
I'm creating a spreadsheet for customers and I'd like to be able to view email conversations I've had with them a little more efficiently. Right now I find the email address in the spreadsheet and ...
- 21
2
votes
1
answer
1k
views
Put the timestamp of last edit on master sheet, next to edited sheet's name
I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set ...
- 21
2
votes
1
answer
2k
views
Any way to separately Validate and create a Dropdowns in Google Sheets?
Sample Scenario:
Sheet 1 has a 3 Dropdowns from sheet 2
Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1
The behaviour ...
- 21
2
votes
2
answers
2k
views
Using Google Sheets to bulk collect the number of Google Search results for given keywords
I would like to create a Google Sheets that will allow me to paste in a list of 25 or so keywords in column A and would then return the number of search results for each of those terms in column B.
...
Jake Havenridge