The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
1
vote
1answer
30 views

Syntax of field “Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is”

In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table. The lowest section of the interface of the editor is Filters (which allows me to add ...
1
vote
1answer
54 views

How to sum values between two dates?

I have a table made up of numbers followed by dates like the one below only with more columns (link below). How would I sum only the values that occur in one specific year? For example, in this case,...
1
vote
1answer
39 views

Need help clearing specific cells in multiple tabs in Google Sheets Scripts

I'm having trouble editing this code to get it to edit multiple sheets at the same time. It works for whatever is active, but I'd like to disable that and have it work on each tab named after each ...
1
vote
1answer
21 views

Google Sheets conditional formatting with AND for value range

I'm trying to set a colour range of conditional formatting based on another cells value. So if A1 is larger than A2 it will be Blue, if A1 is between 0 and A2 it will be Yellow and if A1 is less than ...
1
vote
1answer
57 views

How to automatically fill missing data in Google Spreadsheet

I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically I made a spreadsheet for tracking weight, where each row is one day,...
2
votes
2answers
61 views

How to pull values from another sheet for dynamic Data validation

This one is far out of my league. To simplify things: Sheet1 B4 has a Data validation drop down list Ex. Vancouver, Surrey, Burnaby, etc B5 has a Data validation drop down list Ex. 500, 1000, 2000, ...
3
votes
1answer
44 views

Importing a row from one Google Sheet to another

Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week. Doc2: I have an individual sheet setup for each team. I'm looking to pull ...
0
votes
0answers
26 views

Google Form field ID

I want to link a Google Sheets sheet with a form and pre-populate some of the data from the sheet into the form using this formula: =HYPERLINK("form URL entry.ID="&A1&"&entry.ID,"Link") ...
2
votes
1answer
24 views

Changing a date in google sheets from MM/DD to DD/MM without changing the values

I have a survey data set where respondents DOB was entered as "DD/MM/YYYY". Sheets has formatted some of the cells as "MM/DD/YYYY", and where that wouldn't make sense because the day is greater than ...
2
votes
0answers
13 views

Disappearing hyperlink text in Google sheet [closed]

I have no idea why this issue is occurring, as I have previously had no issues adding links to text in this document before. I update it every year for a sports-tracking related hobby, and have never ...
0
votes
0answers
34 views

Lookup with two criteria

I want to check the Form Response Data based on two criteria. Here is the Form Response Data (on a Separate Tab): I want to fill the 3 Columns (on the Submissions Tab, image further down) with data ...
3
votes
1answer
71 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
0
votes
1answer
21 views

How to give a column a green border if the second row's holds a date = TODAY()?

I'm trying to make a useful schedule sheet to keep track of all of my class assignments: I want to write a script in the script editor that says, for(Column A-Z){ if(A2,B2,etc = TODAY()){ ...
2
votes
1answer
23 views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
0
votes
1answer
20 views

Pushing data from one sheet to another based on a condition, without making sheets dependant on one another

Trying to see if something like this is possible within Google Sheets. I have 2 test sheets that are linked with query(importrange(... And I can have Sheet2 updated automatically when a row in sheet ...
0
votes
1answer
51 views

Use `setValue` to update a column based on values of two others that match their respective values in reference cells

I am new to Google AppScript and would really appreciate some help here. I want to use the setValue along with the for loop and if statement in Google App Script to scan multiple columns in Google ...
2
votes
3answers
33 views

Entering fractions verbatim in Google Sheets

I'm trying to put some marks in a spreadsheet and have a lot of unsimplified fractions or fractions like 19.5/20. Is there a way to enter fractions and have the cells be usable in other formulas, ...
1
vote
1answer
16 views

Hide RUN message in Google Sheets? [closed]

I have created a script on a Google Sheet. Is there a way to hide the balloon message "SCRIPT IS RUNNING" ?
0
votes
0answers
14 views

Auto-updating column in Google Sheet [duplicate]

Trying to figure out a way to have column next to modified column auto-update to show a timestamp of the last update. e.g. 1A is modified and in 1B auto-updated date, 2A modified and 2B auto-update ...
0
votes
3answers
40 views

How to use Importrange in a Query and filter by dates

I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September. I am using the following formula. The other ...
0
votes
1answer
19 views

Highlight row if partial text matches in either of two columns

I have a sheet that has a list of job names, and then the last failure and success of each job. The dates are listed with duration words instead of dates, ie 1 mo 12 days or 3 days 6 hr. I want to ...
1
vote
0answers
51 views

Split rows by comma into multiple rows and divide between splits

I have a Google Sheets spreadsheet (sheet 1) which I use as source for a function to split a row into several rows in a results worksheet (sheet 2). The split function divides the row when it finds ...
0
votes
1answer
15 views

How to duplicate range of cells n-times(range can variate and must be based on column)

Can someone explain for me how with the help of formula I can solve this issue? We have an input range of Tags (letters and numbers). Whole massive must be duplicated in the same order n times. The ...
0
votes
0answers
25 views

Multiple filter Array formula in google sheets

I am trying to get my array formula to be able to filter on multiple drop down menus. I have 2 separate formulas that work but I cannot combine them. Is there a way to combine them to make the Array ...
3
votes
1answer
42 views

Filter and match multiple columns in Google Sheets

I have a list of teams and their corresponding owners in Google Sheets. (owners own multiple teams) I have a great formula that will filter the list by owner name: =FILTER(A2:A6, MATCH(B2:B6,"RINGO",...
0
votes
1answer
24 views

Trying to make a column of “select count” in reference to another column

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
1
vote
2answers
54 views

If a cell is equal to one of the elements of a list then get data from another cell

I have a spreadsheet that gathers data from a Google Form. In this form, we ask the users to choose between the different seating plans. This is an example of the data gathered in the Response sheet:...
1
vote
1answer
23 views

Import cells into the text of another cell preserving quotation marks

Well, I did some research but I didn't find what I'm trying to do. I have this text: "en":"B2", "es":"C2", "it":"D2", "pt":"E2" I have 4 columns with information in each cell. I need to "import" ...
-1
votes
1answer
24 views

How can I make an attendance table that automatically deletes obsolete day and creates a new?

I need a Button that deletes the entry of the first column(A:A) and creates a new one(H:H) and the Date at cell H2 to automatically set to the next Thursday or Sunday. Currently, I just recorded a ...
2
votes
1answer
22 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 ...
0
votes
1answer
37 views

Sumproduct within Arrayformula [duplicate]

This is my formula in cell D2: =SUMPRODUCT(B:B=B2;F:F>F2)+1 The values in column are all of the same length, and are continually updated by contributions from google forms. Therefore a condition ...
0
votes
1answer
29 views

How to copy values that have just been deleted into 'Archive' Sheet?

Setting up so any expired date rows get deleted. But I would Like to Delete and Move to Archive tab (sheet). Have tried taking other codes and integrating but cannot find the code. function ...
1
vote
1answer
37 views

Google Sheets - How to count the occurrence of each day of the week if they are not public holidays?

I would like to count the occurrence of each day of the week in a month if they are not national holidays. In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and ...
0
votes
1answer
30 views

How to combine 3 sheets into one using QUERY(IMPORTRANGE)?

I am trying to combine 3 different sheets of data from range A2:O Col1, Col4, Col5, Col6, Col8, Col11, Col12, Col13, Col14, Col15 using Query({IMPORTRANGE(... function. I tried using the formula below ...
0
votes
0answers
15 views

Plot multiple data ranges in Google Sheets

I'm doing a physics lab in class and we conducted multiple runs of a car rolling down a ramp. For each run, there are two columns of data for time and position. I want to graph all the data on a ...
1
vote
1answer
37 views

QUERY function to search three columns for the same keyword?

I have a table with data in columns A-U. Rows go down to 368. I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...
2
votes
1answer
10 views

Touchscreen tap edits drawing object, doesn't run assigned macro

In Google Sheets, I created a drawing object and assigned it a macro. When I use my laptop in "normal" mode using the touchpad (or at my desktop using my mouse) I can execute the macro by clicking on ...
1
vote
1answer
789 views

Google Sheets function equivalent to Excel's Goal Seek function?

In Excel, there's a function called Goal Seek that will change an input value in one cell such that an output value in another cell hits a certain value. A summary of the function can be found here. ...
0
votes
1answer
17 views

When editing a formula in Google Sheets, how do I resume 'range selection mode' so that I can select a range with the mouse or keyboard?

When you first enter a formula in Sheets you can use the mouse or cursor to select the range. If I want to sum the 5 cells below the cell with the formula, I just type =SUM( and then use the down ...
1
vote
1answer
19 views

Set editing permissions per column, per user Google Sheets

I have a Google Sheet that's being edited by 3 people, but each person needs to edit only one column. EX Red will edit red column; Blue will edit blue column and Yellow, well, yellow column hahaha. ...
-1
votes
1answer
23 views

Add Two Weeks (Weekdays) on Data Validation

I am needing to add two weeks on to date in a separate column. In one column (A) I have the "Date Requested" and in the next column (B) I have "Due Date" I need the due date column to have the "Date ...
0
votes
0answers
10 views

SUM values across different Google Spreadsheets [duplicate]

Is there a way to sum values across different workbooks in Google Sheets? NB - Not different worksheets but different workbooks.
1
vote
1answer
17 views

How do I make the “Text contains” evaluate any word written in the cell?

I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
0
votes
0answers
15 views

Plot coordinate data in a scatterplot in Google Sheets

I'm an inexperienced student using google sheets for my lab report. In the following data, each row represents data for each of 4 people: xi (columns B-E) represents a position in meters, and ti (...
1
vote
1answer
94 views

Finding merged cells in Google Sheets

I'm often trying to freeze columns in a Google Sheets and I get the error as below The way I get around this is to manually hunt for the merged cells and unfreeze them, but this can be difficult on ...
1
vote
2answers
17 views

Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A

I have a list in column A and I want to remove any cells that contain any word.
1
vote
0answers
26 views

Custom Function to Search *another* Sheet for String and Return Value of Next Column [closed]

I'm trying to create a custom formula that can effectively vlookup the value in X cell against a column in another google sheets spreadsheet and return a value in the next column, the same row as the ...
0
votes
2answers
18 views

How to Paste Rows into Google Sheets from Microsoft Works Database

I use an old program called Microsoft Works Database to maintain lists in the form of spreadsheets. After I update a list, I could copy the information to Google Sheets so that I can view them online....
0
votes
2answers
47 views

Extracting last match from a string with REGEXEXTRACT

I've created a simple text filter: =IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE) It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
21 views

How to find the last value in a column when the column is populated from an ARRAYFORMULA

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...