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
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
53 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 ...
1
vote
1answer
20 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
27 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
12 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
682 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. ...
0
votes
1answer
22 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
14 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
72 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
16 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
46 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 ...
0
votes
0answers
53 views

How do I remove a “null” column or legend in Google Data Studio?

I have a simple Google Sheet for my source for Google Data Studio charts. Legends in pie charts and columns in bar charts show a "null" that I can't explain. My columns display correctly but there's ...
0
votes
0answers
11 views

How do I get rid of a “null” in my Google Data Studio charts? [duplicate]

I have a simple Google Sheet for my source for Google Data Studio charts. Legends in pie charts and columns in bar charts show a "null" that I can't explain. My columns display correctly but there's ...
0
votes
2answers
12 views

Auto Sort different ranges

I have a google sheet that I would like to sort different ranges by the 2nd column in each range. Example: A B C D E F G p 7 1 h 9 1 q 6 2 j 3 2 g 9 3 p 7 3 I need the range A:C sorted by B ...
1
vote
2answers
44 views

Inserting error (or spacer) when IMPORTXML can't scrape data in Google Sheets

I am using IMPORTXML to create what is essentially a vehicle inventory feed file. My issue arises when a price is not included in our main website. What seemingly is happening is when the price is ...
1
vote
3answers
47 views

How to use QUERY to show data in Google Sheet using tags

I have a Google Sheet with 2 sheets. In sheet 01 I have this table. In sheet 02 I want to have a "Search by Tag" sheet. So, googling I got this formula =QUERY('01'!A2:D4, "select B,C,D where A = '...
1
vote
2answers
69 views

I am having trouble using indirect to access a cell in a different Google Spreadsheet

I have 2 spreadsheets, Test1 and Test2. In Test2, Cell B2 I want to access a cell in test1. I want to use the value in cell A1 (the value is test1) indirectly to access cell A2 in test1. This is the ...
0
votes
1answer
20 views

Merging cells based on same ID but divided into categories (columns)

I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
25 views

Google Sheets duration format does wrap around 24

If I format the cells of a sheet to duration, it wraps around 24h So adding those three cells (which should represent HH:MM): 05:00 18:00 02:00 with SUM, the result is 01:00 where I need the result ...
0
votes
2answers
30 views

How can I make sure an equation doesn't give an answer if more than one cell is blank?

I have half the equation working: =IF(AND(IF(NOT(ISBLANK(AG22)),TRUE,FALSE),AJ22<AG22),"1","0") So it looks like this: What I WANT it to do, is to only prompt an increase or decrease response if ...
0
votes
2answers
25 views

How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
1answer
34 views

Changing a cell value based on another cell's color?

In my Google Sheets in column F, I want to include the word "LATER" based on magenta color (#ff00ff) that is in column B starting from row 227, how should I write this script? I'll really appreciate ...
0
votes
2answers
24 views

Show values from only rows that have a checkbox ticked

I have a data table with groups as column headers and names and emails as rows. I want to export data to another spreadsheet in the same document, which would only display members who belong to a ...
0
votes
2answers
32 views

Using TODAY function with QUERY

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output." =...
0
votes
1answer
20 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
1
vote
2answers
22 views

Searching a list and replacing value if any item matches any item in the other list in Google Sheets

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
0
votes
1answer
25 views

How To Create a Globally Accessible Apps Script for Docs and Sheets?

I've been wanting to create a script that is accessible from any newly created Google Doc or Google Sheet. Is there any way to accomplish this? For example, if I wanted to create a script that ...
-1
votes
2answers
16 views

Value of Cell A1 could point to values of different cells

I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
0
votes
0answers
34 views

Send multiple emails with the help of Google Script while using different attachments

I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach different PDF/xlsx to this email as per the ...
1
vote
2answers
53 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
0
votes
2answers
43 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
31 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
-1
votes
2answers
25 views

Mark all line if checkbox is true

I will give an example: I have checkboxes in B3:G3. This is a checklist, I want to change the background color to green for A3:H3 if B3:G3 boxes are checked. Can someone please tell me how to do ...
1
vote
1answer
30 views

Visualize Data that updates dynamically

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
0
votes
0answers
9 views

Google Sheet Pivot “% of Parent Row Total”

Can Google Sheets pivot table calculate "% of Parent Row Total"?
-1
votes
1answer
21 views

What do <> (angle brackets) mean in Google Sheet formulas?

What does <> mean in a spreadsheet formula, such as: =A1<>B1