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.
7,264
questions
0
votes
1answer
12 views
With Google Sheets, how to have entered data automatically sent to a specific range group?
I need to fill these price ranges with 100 products each: $10-$20 range, $15-$25 range, and $20-$30 range.
You can see the ranges overlap. When I have a $17 product, I've been manually copying-and-...
0
votes
1answer
16 views
G Sheets - Can't seem to make not contains and is not null work together
I want to return results for col A & B where B is not blank and where F doesn't contain
any city in California so, ', CA'. I can get each operator to work separately,
=QUERY('805'!A1:F1105, "...
0
votes
0answers
12 views
Is Google Apps Script definitely down for me? [closed]
I asked a question which its answers required to use Google Apps Script. At that time, I added my question and awarded the bounty to the answer that seemed alright although I couldn't tried it because ...
0
votes
0answers
13 views
Fill in one cell if another has had a date selected and move the row when the date is selected and subjected to an onEdit script
I have built a Google Sheet with a lot of data. I have also put in a script to sort data automatically, by date, when new information is input. The new information can relate to past, present or ...
0
votes
0answers
11 views
Conditional content in column A based on value in column B, with content determined by column C
I am trying to automate a task where the contents of one column is informed by the (text) value in another column. The content should be pulled from a list in the spreadsheet.
For example:
In this ...
0
votes
1answer
17 views
How to use multiple summations in Google Sheets?
I'm trying to make a system to calculate the Multivariate Hypergeometric Distribution in Trading Card Games. The equation:
In this scenario I have a deck consisting of 60 cards. In this deck I have ...
0
votes
1answer
11 views
Script in Google Sheet to automatically run after importing CSV files to replace current sheet
I'm going to admit I'm a newbie at scripting. I've managed to write the script that I want but I want it to automatically run after importing the CSV file onto Google Sheets.
I have tried a simple ...
0
votes
1answer
18 views
google sheet how to import cells based on value in different cells
So I have two sheets.
Sheet A tab X contains two columns, first one with an url for a google doc and in the other column text IDs.
Sheet B tab Y contains a column with text ID of several more files ...
0
votes
1answer
19 views
Percentile Conditional Formatting in Google Sheets - Copying the formatting but isolating the range
I have setup conditional formatting using a green-red percentile color scale.
This works fine until I copy this section down the sheet. When I do this, the numbers in the second section affect the ...
1
vote
0answers
22 views
Converting VBA code to Google sheets app script [closed]
I have been using Excel VBA code since couple of months and it was working fine now i have to move this code to Google Sheets to access it from anywhere.
I am not an expert but i tried hard to convert ...
0
votes
1answer
17 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 ...
0
votes
0answers
13 views
How to make Row() formula dynamic based on a cell content?
I am creating a multiple choice (see column A) and I want it to be randomized (see column C). I saw a formula in the internet, modified it, and it really does randomized the contents of each cell in ...
0
votes
0answers
16 views
Google Sheets Query - Timestamp [closed]
I will be adding a link to my google sheet at the end of the post. I need to query the data into separate sheets based on the month. I have spent many hours trying to figure this out, any help will be ...
1
vote
2answers
27 views
Array Formula omits IF / AND functions
I'm using a series of IF and IF/AND functions to assign a set value according to, sometimes one set or two set of colums. While creating a range and Array Formula to apply that to all posible new ...
-1
votes
0answers
20 views
Conditional Formatting based on a cell that's only visible in a drop down menu
In Google Sheets, I'm trying to figure out how I can conditionally format a cell within a drop down menu that's based on that same cell from a different drop down option.
For example, I have a drop ...
0
votes
0answers
18 views
Why doesn't my function work in Google Sheets?
=IF(B2=0,0,800*2(B2-1))
Why won't my function work in Google Sheets? Why can't I get an equation to work inside an IF function?
0
votes
0answers
8 views
Custom spreadsheet template hosted in SAAS webapp [closed]
I am building a web application, whereby I like to provide only 1 custom spreadsheet template to the user. The user can edit and fill its contents and save the information. The user can log back in ...
0
votes
1answer
15 views
Publish Google Spreadsheet as a webpage, but a separate address for each sheet
I have a Google Spreadsheet with around a dozen sheets in it. I'd like to publish it to the web, but in a way so that I can give a dozen people each a different URL, each of which takes them each to ...
0
votes
1answer
23 views
Google form save responses in multiple files based on condition on submit
I have this problem:
A Google Form that saves data into one file (spreadsheet)
Multiple users (admins) that access this file (all of them can see all rows)
Let's say first column is called Department ...
0
votes
0answers
18 views
How can I make this custom formula work with multiple columns?
I came across this script that is a custom formula:
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s =...
0
votes
0answers
9 views
Totals on query table
I would like to get totals on the bottom of a table created with a query function. I have managed to total one column but when I try and get a total for the next column it displays the total 1 cell ...
0
votes
0answers
13 views
Automatically sort 1 sheet on edit
I have a google spreadsheet with multiple sheets.
On one of the sheets, we use Zapier to populate new rows of data when a meeting is scheduled with our team. I'd like the sheet "Meetings" ...
0
votes
1answer
17 views
Filter Google Sheet based on multiple checkboxes
I have a Google Sheet with multiple columns filled with checkboxes with each row being for a specific product, above each column is another checkbox and the idea is to use these top checkboxes to ...
-2
votes
1answer
55 views
Search for multiple words in a single cell in multipole columns in google sheets
I wrote the Indexes of my notebooks in Google sheets to make them easy to find. But the Find function only looks for one word in the Google Sheets cell.
My notes look like this:
"Chemistry class ...
2
votes
2answers
85 views
Click link to send a text message in Google Sheets
We are making extensive use of Google Sheets' HYPERLINK formula combined with mailto links to create links in cells that will dynamically generate emails. For example:
=HYPERLINK(
"mailto:...
0
votes
1answer
12 views
Selective printing from a Google spreadsheet
I work in a school and have a teacher who wants to print multiple tabs from a Google spreadsheet. I have managed to show her how to print the pages she wants in the workbook (File, Print, change "...
0
votes
1answer
16 views
Replacing part of a formula using Script
So I usually use template formulas (like - =IMPORTRANGE(LINK,Sheet1!A1) where link is the variable I replace using find and replace).
So, now I wanted to automate this task, i.e. for example the ...
0
votes
1answer
18 views
How to SUM the VLOOKUP results based on condition in other column using ARRAYFORMULA in google sheets?
I would like to sum all the costs based on the products purchased between specific dates.
=SUM(ARRAYFORMULA(IFERROR(VLOOKUP('Purchased List Table'!B2:B,'Pricing Table'!A2:B,2,0),"")))
With ...
0
votes
1answer
12 views
How to add a note or comment into a formula in Google Sheets, when the formula does not return a number?
This question is similar to mine, but all the answers assume that the output of the original formula is a number.
What if I want to add a comment to a query formula that outputs an array of strings ...
0
votes
1answer
19 views
Look up and return line below Google Sheets
Trying to create a "search" to go through a Google Sheet. There are technically two searches. The first one works as it should but the second has issues. Basically, I need the return of two ...
0
votes
0answers
25 views
Is it possible to group GoogleSheet tabs?
I'm trying to find a way to group tabs in folders. I have a range of monthly budgets and would like to group them by year
0
votes
1answer
19 views
Google Sheets - Multiple if statements with and function
I am trying to build a check in/ check out tracker for my team using google forms and google sheets. The idea is that once Someone submits their response through google form, it comes to google sheet. ...
0
votes
1answer
12 views
Help on determine a range of ID's
I would need some help to determine the range of specific id's, like in the picture below the output I would need is in column B, that ID is from A1 to A6. Anyone has any idea to determine this ...
0
votes
0answers
16 views
Google Sheets: Share document with a Variety of Permissions
I want to be able to share a google sheet with my team of 15, have three of us with complete editing capability, five users be able to edit only a single column, and all others viewing privileges only....
0
votes
1answer
47 views
How to speedup Google Sheets script?
I have a script adapted to my needs which was created following this.
I think my script is getting slower over time after adding more entry point in my sheet form.
// Function to submit the data to ...
0
votes
1answer
13 views
Using Google Sheets to document ID swipes from a magstrip reader?
So I work as a student employee at my college and part of my job is to check people in and out. I've been doing this manually by looking at their ID and writing the information, but I am trying to ...
-1
votes
1answer
58 views
Inventory Management where main list highlights items checked out/ in
I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red ...
0
votes
2answers
43 views
How to filter or query only selected columns based on a dropdown
I have a sheet with data, theres a section laid out 8x9 with a layout approximately similar to below (didn't recreate all 9 rows):
Red
Orange
Yellow
Green
Blue
Indigo
Violet
Grey
X
-
X
-
-
-
-
-
X
...
0
votes
1answer
21 views
ORDER BY generating error in Google Sheets' QUERY
I'm getting a '#Value!' error when I try to sort data returned by a QUERY statement. This is the basic statement:
=query('Raw Data'!A:G,"Select A, C, D, sum(G) where B='instagram' and (D='Female ...
0
votes
1answer
17 views
Interactive sheet (by pull down menu selection). Can I embed on website where users use it in isolation (without effect each other in live view)
I created this sheet which displays 2 columns, where the values change, according to some pull down menu selections.
https://docs.google.com/spreadsheets/d/110pWSQX2Kc3WeDvWm4BVwxPdtq2y9wyh39P9wjPVeS8/...
0
votes
1answer
25 views
Conditional Formatting Not Working On My Machine, But Does On Other Machines!
I hope someone can help me with my current issue. I have an issue that my Google Sheets are not working on my Windows computer, but do work when I use the Sheets with other computers!
I make ...
0
votes
1answer
18 views
How to calculate compound interest based on bottom x rows of a spreadsheet in Google Sheets
Let's say I have a table with a percentage interest on each row. I would like to know how to calculate the compound interest based on the bottom x number of rows.
For example, if each row is a day, I ...
0
votes
1answer
36 views
Google Sheets - Return a text value based on another cells text value
I have a set group of "program names" in a column, and want to have another column automatically write the matching "acrononym" for its sister value.
For example: Master in Finance ...
0
votes
1answer
12 views
Stock Inventory Issues
I'm in need of some help with my google sheets project.
I currently run a small business. And use sheets as a way to manage stock. I want to create QR codes to scan on item that will take me straight ...
0
votes
1answer
27 views
Create A Result Limit for Array Column. Spill Into Next Column. (Google Sheets)
I'm looking for a formula that lets me take a dynamic column range and tell it to spill into the next column if it exceeds a certain number of rows.
The most rows I want in one column is 10,000. The ...
0
votes
0answers
28 views
Capture a cell value which updates to a static cell
I have written a straightforward google sheets which dynamically captures closing stock prices in cells in a column for about 30 specific stocks (using their ticker symbol) at the end of the stock ...
0
votes
0answers
12 views
How to have an example cell duration entry of 12:45 format as 00:12:45 instead of 12:45:00?
Inside of a spreadsheet I have, there is a duration formatted column where I do hand entered data a few times per day. This works well for my purposes, but an annoying problem that I face is that ...
0
votes
0answers
21 views
Google Sheets: SUM of the LAST value - apply across multiple columns
Use Case: I have multiple financial portfolios that cannot be connected to via API. I want to track my total balance across portfolios. I do not want to tally every portfolio every day - instead, I ...
0
votes
0answers
12 views
COUNTIFS+importrange
There are two different sheets 1 & 2. Both has columns named "number of files completed". I want to create sheet 3 where it shows number of files completed in both sheet 1 & 2 in a ...
0
votes
1answer
25 views
Formula change needed every 24 rows
I think I'm missing something very basic here :( - My 1st sheet "Time Pred" -> MY SHEET LINK
Trying to get formula in Col E to change every 24 rows- it should refer to Col D cells ...
