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
1
vote
0answers
24 views

How to calculate units with range value of numbers

We sell greeting cards and this is how it works Scenario 1: if you want to send 1 greeting card up to 10 cities then the price will be 15USD if you want to send 1 greeting card up to 25 cities then ...
1
vote
2answers
38 views

Select Unique on 1 Column in Google Sheets - Distinct Doesn't

I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
0
votes
1answer
20 views

How to include VLOOKUP with a loop for a list of data on Google Scripts?

I have a list of data on Sheet1 columns A-N, and a list of updates on Sheet2, columns A-N. I have a code that imports data to Sheet2, and I would like to add to my code a similar function to VLOOKUP. ...
0
votes
1answer
21 views

Conditional Formatting only seeming to apply if the 'Less than' criteria is selected

I am trying to apply some conditional formatting to a cell on Google Sheets. I want the cell to change green if it is larger than another cell, red if it is less, and yellow if it is the same. However,...
0
votes
0answers
19 views

How to send personalized HTML emails from a spreadsheet [duplicate]

I have a Google spreadsheet that contains recipient's names, emails, and a message that anonymous people have sent this Valentine's Day. I am aware of applications such as the various Mail Merges ...
0
votes
0answers
10 views

Google Script protect Row based on cell

How to protect a Row based on a cell "locked", "unlocked" dynamically with Google script?
0
votes
0answers
16 views

I need a script to open a link in Google Sheets, go to that page, copy the URL and paste the URL back to the sheet

I have a list of websites in my Google Sheets with redirect. So I need to manually click on those links > go the website > copy the URL > and paste it back in the sheets. Is there a way to ...
0
votes
0answers
15 views

In Google Sheets, how can I multiply across two rows conditioned on whether a value exists in a given cell?

I have two tables that track sales seasonality as well as sales per person per month: Seasonality: Seasonality Jan% Feb % Mar% 4.5% 6.0% 7.5% Sales: Salesperson Jan$ Feb$ Mar$ John $1000 $1200 ...
1
vote
1answer
32 views

How to express the “*” denoting any text containing part of text when using IF function?

When using this formula: =if(AND('CASES LIST'!R4="No",P5="*neuro*"),"check","") I am looking for the cell P5 if the text contains "neuro" in the text ...
0
votes
0answers
37 views

Using a list of ID's and a List of conditions COPY and Paste

I have a spreadsheet a list of 15 spreadsheet that I have to update weekly with fresh data. At present I do this manually however having a script will make the task so much easier. What I have is a ...
0
votes
2answers
39 views

Merge two Tables and sort

I have two tables in two different sheets of my Google Spreadsheet that contain the same information. I now want to have a third sheet where I combine these two tables into one and sort them by a ...
0
votes
1answer
29 views

Count cells across columns based on one column

So we are opening up school again and I am trying to get some data from a survey sorted. I have two sheets, so I will focus on one right now. I have a formula that works but doesn't match the other ...
0
votes
1answer
27 views

Sheets ArrayFormula with SUMIF

In a 'Records' sheet I have columns of timestamp, a duration, and a category (i.e. 13 minutes of 'B' was done at 18/01/2021 00:00). In the 'Analysis' sheet I have a field where the Type can be ...
1
vote
1answer
20 views

How to create an average value for every cell which contains a certain phrase?

I need to find the average value of cells containing a certain set of initials to create a scoreboard. The value of the cell being the score and the brackets containing the initials of the score ...
0
votes
1answer
27 views

Google Sheet auto fill for 360 rows?

Summary of the question: if we have 1 in cell A1, and 2 in cell A2, is there a way to make 1 and 2 continuously fill to A1000, with 3 in A3, 4 in A4, ..., and 1000 in A1000, without the user dragging ...
1
vote
0answers
17 views

Function(“url”) With A1 Cell Contents as Part of URL Query

I am trying to import JSON data into Google Sheets essentially like IMPORTHTML("URL"&A1&"URL"). Basically trying to adjust the URL by the contents of Cell A1. It seems to ...
0
votes
0answers
15 views

Conditional Formatting in Google Sheet

On Google Sheets, what formula do I use if there is something in the cell of range F and the corresponding cell in range G is empty to that list "No Content" in the corresponding cell in ...
0
votes
0answers
13 views

In a sheet which has tab names as date, can I empirically refer previous sheet, so I can calculate report based on yesterdays

In a sheet which has tab names as date ., can I empirically refer previous sheet, so I can calculate report based on yesterdays? =IMPORTRANGE("Link",""&BM4&"!AU:BC&...
0
votes
0answers
24 views

Would it be possible to sum by (TODAY(),“MM/DD/YY”) as the rows are typed manually?

Please look at this Google Sheet https://docs.google.com/spreadsheets/d/1pOm6tWUHI9xoJuXuu64vQT7QryqpBQxYBMce7NAcgEY/edit#gid=0 Would it be possible to "dynamically" insert the total for ...
0
votes
0answers
10 views

Google Sheets email notification

I have a spreadsheet that is being populated by a Google Form, I need to set up so that an email notification is sent if the word “other” is imputed into the sheet, any help?
-1
votes
1answer
19 views

Formula to Copy Paste (instead of Vlookup)

I have a very important issue. Is there a Formula that works like VLOOKUP but does not change the values dynamically? Instead of changing the values, it just copies and pastes the value each time the ...
0
votes
0answers
14 views

Split choice grid or multiple choices in several rows on sheet

I have a form that I want to work as follows: The person will answer a question (text box) with an ID and another question (which can be a choice of multiple answers or a multiple choice grid). What I ...
1
vote
1answer
48 views

How to filter blanks in a table using ARRAYFORMULA

I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet: I am filtering the non blank ...
2
votes
1answer
27 views

Searching cells and pulling values in Google Sheets?

sorry for the vague title but I'm not sure how exactly to describe this. I've created a spreadsheet and used the Coinmarketcap API to pull lots of data, here's the relevant part: From another sheet I ...
-1
votes
0answers
18 views

How to make the a checkbox of a cell undeletable?

Is there a way to make checkbox undeletable.. you can check/uncheck but you cannot delete. Or show check box whenever a specific cell is not empty. I found this post https://stackoverflow.com/...
1
vote
0answers
21 views

Google Sheets conditional formatting adapt to value

I'm currently working on a Google Sheet which tracks hourly performance, the layout is basic which is hours against units completed. The conditional format I already have in place works for a time ...
1
vote
0answers
15 views

Change the text string of a cell based on whether or not previous cells are filled

I am working on a spreadsheet that tracks the overall pipeline for video production for a group of volunteer filmers and editors. Being on as a volunteer manager myself, I wanted to create a ...
0
votes
0answers
12 views

Array formula-ing this formula breaks it…any ideas why?

I have a spreadsheet with SheetA and SheetB. I'm trying to pull into SheetA some of the columns of each row in SheetB where the date in Column I of SheetB is earlier than today. This formula on a ...
0
votes
0answers
7 views

Creating graphs from own formula

We are all familiar with the Y=mx+b Now I have the following values that I want to use in this formula to create a graph in Google Sheets: m = slope k = exponent b = y-intercept (vertical shift) c = x-...
0
votes
0answers
42 views

Find average number of ranges including current date over a period

I take notes in Google Sheets of the dates when I buy and sell stocks. I would like to know the average number of companies I own stock in on a day. I have one column for the buy date, and another ...
0
votes
0answers
14 views

How to import data when Google Sheet address changes each time the source data is updated

I can export data from my ERP system to Google Drive. But each time that I export the data a new sheet is created with the same name. For example I export a "purchase report" in January, and ...
0
votes
0answers
10 views

How can I delete a pivot table in Google Spreadsheets? [closed]

Is it possible to delete a pivot tablet without deleting the complete tab?
1
vote
0answers
10 views

Restrict editors to only able fill today's date in data validation date - Google Sheet

I have used the data validation date > 'valid date' so that in the range of cells I want, will already show the calendar drop down. However, would like to ask for guidance on how I can restrict the ...
0
votes
1answer
32 views

Google sheets: summing cells in a column where the cell above contains a string

I'd like to sum the cells in a column where the cell above contains a string. For example, sum all the cells where the cell directly above contains the string "Transport". For example, in ...
0
votes
1answer
24 views

Google Sheets script not working if new script made for new sheet?

I already have a script which is working fine in on sheet. But when I want a similar script working in another sheet. It won't work. Has anyone experienced such a thing before?
1
vote
1answer
15 views

Nesting if statements

I'm trying to nest the following IF statements. In order to have a different word returned according to what word is in the other cell. =(IF((D2 = "Your Spouse / Partner"), "my partner&...
3
votes
0answers
20 views

“You have reached your limit of named versions” when naming a version

I didn't realize this, but Google Sheets (and I'm guessing Google Docs) as well seems have a limit on the number of named versions you can save? The bigger issue is that I'm not able to find any ...
1
vote
1answer
54 views

How to combine these formulas in one conditional formatting rule

I am checking if column S is empty & Column Q="Consultation" then I need to set a rule in a cell to make conditional formatting in cell A1. So these are my steps: Step 1: in X column: =...
0
votes
1answer
25 views

Query using starts with

A little help to do the following: I am good with Queries however, can't find if it is possible to do the following: =Query(range, "Select * where B begins with (**want a range from A to Can**) ...
0
votes
1answer
17 views

IF, THEN formulas in Google Sheets

I have a table with grade levels of students in column C and the total duration of time earned (sum of D3:F3) in column H. In column J, I want to calculate a percentage of the total time earned based ...
0
votes
0answers
17 views

Setting up multiple validation criteria in Google Sheets

I have a list of metrics that I want users to prioritise by ranking their top 20 ensuring that there are no duplicates. There might be a simpler way to do this, but what I've come up with is to use ...
0
votes
0answers
22 views

Script For Google Sheet With Changing Names

We have a Google Sheet and the names of the Sheets will change based on the date. So, we will have a string of Mon 2.15 Tue 2.16 Wed 2.17 Etc... The sheets are copied and then a new name given to ...
0
votes
0answers
23 views

Split rows into separate columns

I have data in one column as follows: website.com/US/x=abc website.com/US/x=pqr website.com/BO/x=avgdf website.com/BO/x=jeid website.com/RO/x=klhg website.com/RO/x=dfg website.com/CA/x=rfd website.com/...
1
vote
1answer
34 views

Can a formula cell reference be a variable cell reference?

Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as ...
0
votes
0answers
12 views

Apps Script for Google Sheets

How do you extract data from an active cell? Like for automating a chart you have to go in and specify the range before running it.
0
votes
1answer
44 views

Can this Google Sheets (Day/Month tab) be simplified?

Column:Row G1, H1 create the data from Sheet1 and J2 formats it for cell A1 in Weekday order. Please note also cell A12 which creates a Monthly summary directly from Sheet1. Can this sheet (Day / ...
0
votes
1answer
37 views

How to insert more than 10,000 images in Google Sheets

I have inserted over 10,000 images into my Google Sheet (mostly Rubik's cube algorithms and their cube diagram). They are 40-45KB PNG image, and I use the operation, Insert -> Image -> Image in ...
0
votes
1answer
15 views

Using Google sheet checkbox as conditional in calculation

I am trying to have a Google Sheet checkbox and do some calculation based on it. Ex (pseudocode): if B1 is checked: A1=(B5*A20)/12*100 else A1=(B5*A20)/12*200 Is there a way to achieve this?
1
vote
0answers
20 views

How to Use Google Sheets' Macro to Copy Data from Volatile (randbetween) Cell

What I would like to be able to do is execute a macro that copies the data from a cell with a randbetween formula, and pastes that data (copy/paste values only) into another cell. When I attempt this ...
0
votes
1answer
19 views

Auto generate named range when a cell is filled

I'm interested in auto generating named ranges for the sake of easy variable creation for formulas. I generally use a tab called CONSTANTS while building calculators to help keep the data clean and ...