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.

Filter by
Sorted by
Tagged with
2 votes
3 answers
10k views

Google Sheets Query - True/False/Other

I'm trying to get a query based on a tri-state. The possible values are TRUE, FALSE, 'Ignore' or blank. I want this to select all names (B) and emails (C) where a flag column is set to False or ...
0 votes
1 answer
21 views

How to merge multiple similar rows into unique rows in Google Sheets?

We have a task in hand. Current Google Sheets look like this: We want to make Google Sheets look like this: What are we currently doing? Select all similar rows Click on format menu -> merge -&...
0 votes
1 answer
39 views

How do I advance the column instead of the row when I copy/paste a formula?

So pretty simple thing I am doing, pulling data from a form and putting it in a template. =('Form Responses 1'!B3) is what I am doing, which works fine for my needs, however when I copy and past ...
0 votes
2 answers
8 views

unique formula, to automatically add category of exercises from different columns into one list

I had a bunch of exercises listed on a sheet named 'exercise sheet', each in its own category. On a different sheet (my programming sheet), I used the formula =unique('exercise list'!B3:B) to pull out ...
1 vote
1 answer
4k views

Google sheets, scatter plot, multiple datasets, with datapoint labels

I want to create a scatterplot in Google sheets with multiple datasets, and yet still be able to add data labels for each data point. Additionally, it's two independent axis, which means some X values ...
0 votes
0 answers
9 views

how can i sum cells that have an inexact range?

I want to sum the number of reps on the RHS as a total, taking the top value that is optional to athlete performing the set. so 8-10 appears 5 times, 50 reps total...how do i make google sheets do a ...
0 votes
1 answer
24 views

Get exactly data to put on email message?

I got a function to email notice like this code below: function approvedby() { var approvedrange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses 1").getRange("E2:E500"); var ...
0 votes
1 answer
11 views

How do I sum values based on criteria in the same row?

how do I sum values horizontally that match the word "food" in cell A1. example below:
0 votes
1 answer
111 views

Partial Font color in a cell

Wanted to see if there's a way to automate changing the color of the font in cell for selective parts. For instance, I have in one cell: If you have any questions, please <a href="{linker('...
0 votes
1 answer
761 views

Getting a direct image link with file extension to a published Google Sheets chart

I have a Google Sheet with a chart built from data in the sheet. I've published the chart, but the URL isn't an image link. I need it to be a direct link to a valid image format (either .jpg or .png). ...
0 votes
1 answer
331 views

Multiple timestamps in multiple columns on multiple Google Sheet tabs

I am trying to work out what script is required to have multiple timestamps on several tabs however each one I have tried doesn't work for me. I am new to this. When text is added to column 2 I need ...
1 vote
1 answer
2k views

Can automatic send emails when checkbox = True?

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly ...
0 votes
1 answer
793 views

Google Sheets Cell Reference +1

I am wanting to enter a bunch of cell references that are affected by a change in only one of them. On one sheet tab, I have a list of dates in column C, and dollar figures in both D and E. In another ...
0 votes
1 answer
17 views

Correct Google Sheets Autofill - Skipping Cells

I'm trying to have a spreadsheet with a second standings chart that references a lot of different information on the sheet; I'm trying to average a range of a row of cells, but the rows that have the ...
1 vote
1 answer
20 views

How can I shorten this formula in Sheets?

In column A I have a long list of words (one per cell). My goal was to determine which words are made up of the same letters (e.g. Align, Lagging, and Ganglia, so repeated letters don't matter to me). ...
0 votes
1 answer
138 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
1 vote
1 answer
12 views

Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
0 votes
1 answer
361 views

ARRAYFORMULA and FILTER

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing In the first sheet ("Matrix"), I have ...
1 vote
2 answers
379 views

What function can I use to conditionally format text depending on cursor position or active cell?

In Google Sheets, I have a 'Freeze' on Column A. I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X. Is there a way to manage that?
0 votes
2 answers
61 views

Google sheet pop up info

I use google sheets for woodcrafter members to log time to use our woodworking shop. The shop has a keycode entry and members are only to enter if they have booked time. All members were given access ...
1 vote
1 answer
68 views

Formula to ignore blank cells and calculate cells having numbers only

I am looking for a Formula to calculate cells having numbers only and ignore blank cells. The condition is between two cells, IF there is a value in A column then ADDITION should take place or IF ...
1 vote
0 answers
10 views

Search box to find if number entered falls in a range

I have a Google Sheet with a number in column B and another in column C. It is a number range that is produced daily. I am trying to create a search box that a user will type in a specific number they ...
0 votes
1 answer
23 views

Import a working formula from a different Google Sheet

I want to import a working formula from doc 1 to doc 2. I used importrange but then it importet the data from doc 1 to doc 2 and not the formula. I then used a second spreadsheet in doc 1 to show the ...
0 votes
2 answers
3k views

Changing cell based on date in another cell

I'm trying to work out a price sheet for some equipment I'm buying. I would like to have a cell change it's date based on today's date that's in columns so it can show me today's date as a quick ...
0 votes
0 answers
16 views

Auto create new sheet when new data in column?

I'm trying to create a new sheet on google sheets whenever column B gets a new value. This sheet will be named the value that created it. . I also want to have a function in the new sheet that ...
1 vote
1 answer
7 views

How to return a value from a row from the last column a text appears

I am having problems returning the correct value i need. I enter data on this table. And i want to return some values on the other table. In that second table i want to return the last date his name ...
0 votes
1 answer
32 views

Comparison Column Chart

I'm trying to make a chart in Google Sheets to compare how much two people have paid towards something. What I'm looking for is the chart to have several cost categories (groceries, dining out, pets, ...
0 votes
1 answer
24 views

How to make formula based off a withholding table?

I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
0 votes
1 answer
24 views

if and for statements on Google Sheets with multiple tabs

I am trying to figure out how to send an email to a bunch of people on my mailing list, but with it being individualized for each person. I have their names and email addresses on one Google Sheet (...
0 votes
1 answer
17 views

How to Turn Data in Rows into Data in Columns but only when the rows share a key element in common (Pivot a table into a matrix?)

My student information system gives me multiple rows per student, each row showing one period in their schedule. I want rearrange the data so each row is the complete schedule for one student. Put ...
0 votes
1 answer
23 views

Creating custom month range instead of 1-30 need it as 26-25

For example, if we use the Text function to retrieve a month from a date. It will show the value as per 1-30 but what if we want our month to start from 26th to the 25th of next month and count this a ...
0 votes
1 answer
26 views

Any way to access the filter funnel icon via keyboard in Google Sheets?

It's not in the official docs, but is there by any chance a non-documented/hacky keyboard shortcut for the little green filter funnel which has to be clicked to bring the "filter by" menu? ...
0 votes
1 answer
9 views

Why does my onEdit macro error when checking on an edit in a specific cell and sheet?

I've been battling with this for several days and used many previous answers found on these site, as well as a few others, but still can't get my script to run as intended. I have a dropdown box on ...
0 votes
1 answer
22 views

Google Forms corresponding to a pre-set date on Sheets instead of being added consecutively

Is it possible to have Google Form data inputs being added not one after the other but corresponding with the Date that is pre-filled on the response form? Here you can see how the data is currently ...
1 vote
1 answer
5k views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
3 votes
1 answer
404 views

Why do Google Sheets filter ranges sometimes auto-expand to include new data, and sometimes don't?

When I create a filter in Google sheets, I want the range to automatically expand as I add/populate new rows. Sometimes, this works fine: When I have a column using “Checkbox” data validation (...
0 votes
1 answer
22 views

Can I add another, different set of data to a row or column in the same sheet?

I am trying to enter some analytics data from Twitter. I want to sort things by month. In each month, I want to have columns for 1st best tweet, 2nd best tweet, etc. In each of these tweet columns, I ...
0 votes
1 answer
1k views

Using checkbox to copy data form worksheet A to worksheet B

I am trying to use the new Checkbox function in Google Sheets. I can't figure out how If I click the checkbox in A9 on Worksheet 2 I want the Text in A10 and the $ amount in A11 to be copied to cells ...
0 votes
2 answers
1k views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
1 vote
1 answer
41 views

How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
1 vote
2 answers
65 views

Changing the dot (.) into comma (,) in Google Sheets, how do I do that? (numerical pad, in Holland, for currency)

I already managed to change the dot on numerical pad into a comma in i.e. excel. But how do I do that in Google Sheets?
0 votes
0 answers
11 views

Google Sheet Formula / Appscript with Google Form Results

I'm creating an automated Roster which takes Data via Google Forms. The Goal of this is for the Data to go into the Sheet that Google Forms Makes automatically. The issue I'm having is trying to get ...
0 votes
1 answer
33 views

Sort a column by name with query between tabs

I am pulling information from one tab to another and I believe this is why it will not allow me to sort (because it is not sorted in the original tab). The easy solution would be to sort that tab. ...
1 vote
1 answer
32 views

Organizing hierarchies in Google Sheets

I am currently using Google Sheets to organize and update the keywords I use in Adobe Lightroom (Photo organizing software) I'm trying to organize my keywords into hierarchies to make it easier to ...
0 votes
1 answer
18 views

How to find the next lowest available number from a list on Google Sheets

I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500 As an example if 1 has been used the next available number will be 2. However, ...
0 votes
1 answer
42 views

IF/AND functions from multiple cells

Would anyone know how to fix this problem? I want cell A676 to categorize based on info from 2 cells C676 and D676. Basically IF the word "Paypal" is in cell D, AND there is a specific ...
0 votes
1 answer
201 views

How to Run a Query in Google Sheets for New 26-Week Highs 2 of Last 4 Days Using GOOGLEFINANCE Function

I already have a query that returns a list of 26-Week Highs (from a sheet with my list of stocks (Column A), column of today's high (Column B), column of 26-Week High (Column C)). I'd like to have a ...
0 votes
2 answers
1k views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
0 votes
1 answer
20 views

Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
0 votes
1 answer
27 views

Google Sheets: Determine eligibility for a certification based on fulfilled requirements

Problem overview I have a list of certifications that have a set of requirements (e.g., years of experience) that applicants are required to fulfill to be eligible for the certification I have a list ...

1
2 3 4 5
160