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,630
questions
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 ...
