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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
6,878
questions
0
votes
1answer
16 views
Google Sheets - COUNTIFS with timestamp
I have a column in a table that contains the date and time. This is the form
15.06.2020 09:43:24
17.06.2020 08:21:04
18.06.2020 12:11:34
or also
2021-01-21 11:57:34
2021-01-21 11:58:02
2021-01-21 11:...
0
votes
1answer
11 views
Repeat values a variable time on each row in Google Sheets
I'd like to automatically populate a dynamic list where values are repeated on each row separately. The number of repetitions is in column A and the value to repeat is in column B. The formula should ...
0
votes
1answer
17 views
no query result when one of the columns used in the output never has a value
I have a query where the output involves summing two columns. If either of the columns never has a value (given a set of filter criteria in the where clause), there is no result. Why?
Here is an ...
0
votes
0answers
28 views
How to make Pivot Table repeat row labels *without* expanding all columns?
I am making a spreadsheet for doing some analytics and determining win, lose, or draw percentages. The pivot table is all fine, except that Column A is a date column, and if I choose to not have all ...
0
votes
2answers
29 views
Generate sequential numbers in first column based on value in other column
I'm having a problem implementing a spreadsheet with several sheets that use data registered continuously on the first.
I found a solution with this script on the forum but I don't know how to edit, I'...
0
votes
1answer
13 views
Counting a cell if another cell has certain strings
Have a cell which will either have W, L or BB in it.
I want to add the cell in O6 if the N6 cell contains any of the strings mentioned below what is the code for this?
below is what I currently have ...
0
votes
0answers
30 views
Sequential update of Google Sheets
On page 1 of my spreadsheet I have a bunch of Google Finance functions to retrieve historical data of various scrips. On page 2 I have some calculations running based on the historical data and on ...
0
votes
1answer
15 views
Combine rows that have different values into one row for the specific value in column A
I have a big list of items that unfortunately split the items into 2 rows but with differing values in different columns. Is there a way to combine them into 1 single row like this?
Any help much ...
0
votes
0answers
11 views
Pull Steam name and Numerical ID with IMPORTXML in Google Sheets [duplicate]
I'm trying to create a an IMPORTXML forumla that will pull a Steam name and numerical ID from a profile and paste it into the cell in the following format:
SteamName (123456789)
I tried this formula ...
0
votes
1answer
20 views
Simplify Google Sheets Sum Formula?
This might be a super easy fix.
I'm just getting started with formulas and I want to simplify my document so it can run a little faster.
I have a big sum formula:
IF(D2<>"X - Done";C2;...
0
votes
0answers
12 views
How to fill a column with a default value of 0? [closed]
I got a google spreadsheet with 6 fields. How can one make a column have a default value of 0 that an user can change if he wishes to?
0
votes
1answer
14 views
Display data on its respective sheet/tab if it matches value
need your help once again to solve a mystery
If "CleintList" sheets mention a service then display it on another sheet.
Example:
if it says, "WebDev" then add it to the WebDev ...
0
votes
0answers
8 views
=IMPORTXML() XPath_Query Question [duplicate]
I'm wondering what I should use for my "XPath_Query" in google sheets if the highlighted text is the text I want it to pull up:
I tried " =IMPORTXML(C124,"//h1[@class='...
0
votes
0answers
19 views
Google Sheets Checkbox Query/Pivot/Other Question
For my pizza hobby I keep track of the pizzas I like to make and the ingredients in them. I have a good sheet with the following data.
I would like to make a view of the same data where I can see ...
0
votes
1answer
72 views
+100
Can I implement this moving average with missing periods using ARRAYFORMULA?
I have these columns:
A person_id
B date
C cupcakes_eaten
D date-35 (35 days prior)
There can be multiple rows with the same person_id and same date.
There can be days with zero rows for a ...
-1
votes
2answers
27 views
ReferenceError: MONTH is not defined
Using the following Google Sheets script:
var currentMonth = MONTH(TODAY);
I receive the error ReferenceError: MONTH is not defined.
Why is MONTH not defined?
1
vote
1answer
29 views
Does google sheets do “lazy” calculation?
E.g. In gnu make, the program keeps a dependency tree for what files depend on what other files. So if one file is changed make doesn't have to 'make world'.
In Sheets does it keep a topological ...
0
votes
0answers
11 views
Distributing a google table between multiple sheets [duplicate]
I have to keep track of number of hours spent on tasks per person. In order to make everything more readable, instead of putting it all in a single sheet, I'm dividing tasks into categories and ...
0
votes
1answer
16 views
How do I chart timeseries data organized by one of the columns?
Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done.
I want a chart with time as the X ...
-1
votes
1answer
27 views
how to select and automate “copy by value” to persist data in Google Sheet
I would like to use Google Sheets as a form to query for data, select and store the data I'm interested in.
Below what I'd like to try. Please let me know if feasible, and if so, could you tell how to?...
1
vote
3answers
34 views
'contains' and 'starts with' cannot match text with a character after a hyphen
Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing ...
3
votes
2answers
119 views
cannot drag query across multiple rows (overwrites data)
Is it possible to click and drag queries?
I describe my problem below and also have a reference sheet.
cells A1:B5
Category
Withdrawal
CatA
4
CatB
2
CatA
6
CatB
11
cells C1:C3
Check For
CatA
...
0
votes
0answers
17 views
How to delete a built-in Google Docs keyboard shortcut
I'm a French minor, so I often use the ENG INTL keyboard to write in French. Unfortunately, the shortcut to write « » is the same as the Google Docs shortcut to change the zoom to "fit": ...
0
votes
0answers
13 views
Double lines division representation [duplicate]
How to make a fraction to display a numerator on top of the denominator in Google Sheet?
0
votes
1answer
21 views
How to DSUMPRODUCT?
I have a table that has the following layout, and is quite long.
Quantity Price
5 10
14 5
10 7.5
I need to get the sum of the product of each line. Unfortunately, I can'...
0
votes
1answer
24 views
Arrayformula with large function for multiple columns in Google Sheets
I was wondering if anyone had a method for finding the second or third largest numbers in a row across multiple columns using the large function in Google Sheets using an arrayformula to account for a ...
1
vote
1answer
26 views
How to convert text to a number in Google sheets
I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
1
vote
1answer
25 views
How to remove duplicate text in a cell?
I have names and email addresses that are repeated within a cell. Is there a way to just keep unique names and addresses in the cell? For example:
Currently A1=martha stewart martha stewart ms@site....
1
vote
2answers
30 views
Google Sheets reverse vlookup partial match?
I'm trying to return the length, width, height, and weight for each item (A1:E29) based on the data provided (G1:K10)
I know how to do a vlookup partial match but this is the other way around.
https://...
2
votes
1answer
76 views
Is it possible to make the most recently-created sheet the default?
In Google Sheets, if you have a Spreadsheet, then you can make individual Sheets (like tabs) within it. In my Spreadsheet, I've made a new Sheet every month for the last four months, so January ...
0
votes
1answer
20 views
Is there a way to get, programatically, the datetime of the last update to a google spreadsheet? [closed]
In our system we have google spreadsheets which content editors use. I have some processes which pull these down and save them as local csv files, so the data can be read in by the system.
It would ...
0
votes
1answer
29 views
Best way to change dates in Google Sheets in all the tabs?
I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
0
votes
1answer
22 views
How to create a IF formula that searches for multiple (more than three) text options
I'm trying to create a Google Sheet to track pitches (I'm a baseball coach). I have a current sheet that I have a check box to click if the pitch was a strike or not, and then you have to choose a &...
0
votes
0answers
15 views
Sort one column in multiple columns
I have a Google Spreadsheet with the information from a Google Form. The sheets updates automatically. I have a column with the hour of delivery. Now I want to sort that info in a new sheet. I can't ...
0
votes
0answers
22 views
IF function that leaves the last number calculated if statement is true/false
I am trying to write a formula that updates a number in the cell if another cell is empty, and leaves the last number calculated if the other cell is not empty. I am calculating my profit, which ...
0
votes
0answers
19 views
Formula to Reference an Outside Sheet If Specific Text In Cell Range Is Present It Will Compare Text in Two Ranges and Populate Text
I need a formula to reference an external workbook for to query a cell range for a certain entry to exist, and if that specific text is present, it will cross reference the a cell from a certain ...
1
vote
1answer
32 views
Is there a keyboard shortcut to 'focus' cell I'm typing in so that the left/right buttons don't shift to adjacent cells?
I'm on a Mac, using Chrome. When I type abcde in a cell in Google Sheets, and then want to move the cursor to the left within abcde in order to backspace c, I have to manually click the cell to focus ...
0
votes
0answers
13 views
How to embed table from Google Sheets to Google Doc and preserve formulas & cell protection
I'm created a Google Doc posted to the Canvas platform for individual students to record data from an experiment. (see below for screenshot)
In excel, I can easily
insert AVERAGE formula for each row ...
0
votes
2answers
25 views
Why doesn't “format A 'yyyy-mm' options no_values” strip the day from the output?
I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to ...
0
votes
0answers
15 views
Getting the latest date of something happening (with conditions)
So basically, I've got two sheets. One (sheet_1) with every single record in the system (over 11000 rows) and the other one (sheet_2) is querying information from that sheet. The relevant information ...
1
vote
1answer
35 views
Google Sheets: VLOOKUP formula for a multi line cells range
I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.
In the RECORDS I have a list of names, in alphabetical order, and a ...
0
votes
1answer
21 views
Finding the longest time duration in Google Sheets
I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time.
=LARGE(...
0
votes
1answer
25 views
How to find and get data from only a certain, dynamic set of columns in another Sheet?
I saw a proposed solution here, but it didn't work for me.
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
Using formula:
=SUM(INDIRECT(ADDRESS(4, MATCH("...
0
votes
1answer
11 views
attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet
I found a proposed solution on Stack, but I can't get it to work.
https://webapps.stackexchange.com/q/120111/270308
Starting formula:
=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...
0
votes
1answer
12 views
How to add arrayformula function to this formula with IF function?
I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
0
votes
0answers
30 views
Import data from a different file but only if it matches some of the data in the new one?
I would like to copy information entered from another Google Sheets file but only if it relates to the information in the new file.
For example, MR SMITH's employee number is entered into one sheet ...
0
votes
1answer
15 views
Range returning null using getRange
I am new to Google Apps Script and the docs are a little fuzzy on the returned data from a method call. I want to loop through a range, but it is coming back as null. What am I doing wrong?
var ...
0
votes
0answers
26 views
Formula to Have Sheet Populate Data From Another Sheet If Referenced Cell Exists in Import Sheet
I am doing my best to write formulas, but I am still super new to it all. I am doing my best to figure out how they work and I am stuck at the moment.
I am looking for a formula that can reference ...
0
votes
1answer
7 views
problems with form and appendRow
I created a form in Google Script and one of the fields is a Checkbox, the name of the field would be field1 []
and to save the answers in a sheet, I am using AppendRow, but when I insert the name of ...
0
votes
1answer
26 views
How to Import Data From Another Sheet If Any Data is Present In A Cell Range Corresponding to The Same Row
I am extremely basic in my understanding of coding formulas for Google Sheets, so please help where you can. The formula I'd like to put together is to search a vertical range (column) of information ...
