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
0
votes
0answers
16 views

Getting the latest date of something happening (with conditions)

So basically, I've got two sheets. One (sheet_1) with every single record in the system (over 11000 rows) and the other one (sheet_2) is querying information from that sheet. The relevant information ...
1
vote
1answer
35 views

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET. In the RECORDS I have a list of names, in alphabetical order, and a ...
0
votes
1answer
24 views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
0
votes
1answer
25 views

How to find and get data from only a certain, dynamic set of columns in another Sheet?

I saw a proposed solution here, but it didn't work for me. How to use arrayformula to find and get data from only a certain, dynamic set of columns? Using formula: =SUM(INDIRECT(ADDRESS(4, MATCH("...
0
votes
1answer
11 views

attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet

I found a proposed solution on Stack, but I can't get it to work. https://webapps.stackexchange.com/q/120111/270308 Starting formula: =IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...
0
votes
1answer
12 views

How to add arrayformula function to this formula with IF function?

I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
0
votes
0answers
30 views

Import data from a different file but only if it matches some of the data in the new one?

I would like to copy information entered from another Google Sheets file but only if it relates to the information in the new file. For example, MR SMITH's employee number is entered into one sheet ...
0
votes
1answer
15 views

Range returning null using getRange

I am new to Google Apps Script and the docs are a little fuzzy on the returned data from a method call. I want to loop through a range, but it is coming back as null. What am I doing wrong? var ...
0
votes
0answers
26 views

Formula to Have Sheet Populate Data From Another Sheet If Referenced Cell Exists in Import Sheet

I am doing my best to write formulas, but I am still super new to it all. I am doing my best to figure out how they work and I am stuck at the moment. I am looking for a formula that can reference ...
0
votes
1answer
7 views

problems with form and appendRow

I created a form in Google Script and one of the fields is a Checkbox, the name of the field would be field1 [] and to save the answers in a sheet, I am using AppendRow, but when I insert the name of ...
0
votes
1answer
26 views

How to Import Data From Another Sheet If Any Data is Present In A Cell Range Corresponding to The Same Row

I am extremely basic in my understanding of coding formulas for Google Sheets, so please help where you can. The formula I'd like to put together is to search a vertical range (column) of information ...
0
votes
1answer
14 views

Google Apps Script MailApp Not Sending Email To Script Owner

I've set up a script to take information from a Google Form and email that information to multiple people using Google Apps Script. What it does not do, however, is email the owner of the script. I'...
0
votes
1answer
16 views

Multiply on a Specific Date (investment simulation for students)

I am trying to create an investment simulation for my high school students in Google Sheets. They already have a bank account that they use (in google sheets) for reward for specific behaviors. I ...
0
votes
0answers
9 views

Dynamic Line Charts for Stock Performance Overtime

I've been riding the struggle bus for a few days now trying to figure out dynamic charts. I am trying to create a dynamic chart that will show stock data for specific increments of time. (30, 60, 90 ...
0
votes
1answer
11 views

Google Sheets Google Forms updates

I have created a Google Form for which collects data from a user. Once the use submits the form I have scripts that copy the data to several other Google sheets and then send an email to the ...
0
votes
2answers
20 views

Dynamically form a cell value in Google Sheets

This might be very trivial, but I was not able to obtain any concrete result from my Google searches, so asking here. I want to fill a column with the content of some other column (in some other sheet)...
1
vote
1answer
21 views

Using a custom formula on a sheet that is constantly expanding

A custom formula for 5 possible colors, by day is used to highlight entries by DayOfWeek Is it possible to use a format like A2:A instead of manually editing it as the sheet grows bigger? Formula ...
0
votes
1answer
33 views

Can a publicly shared file stored in my Google Drive be deleted by someone having “Anyone with the link” editor rights?

Following a concern expressed by a user at How to share a Google Sheets demo spreadsheet. The problem with sharing it editable is that anyone can trash the sheet. Surely it's better just to share it ...
0
votes
1answer
10 views

Send email when cell value equals 29 [duplicate]

I am having trouble with my google sheets script and I wondered if anyone had any advice for a beginner. I want my script to check a cell value (Sheet1, H2), then send an email if the value = 29. ...
0
votes
2answers
18 views

Function/formula to return only the first string proceeding the first carriage return

Is there a function/formula to return only the first string proceeding the first carriage return using Google Sheets? Example Text: Bob & Mary Smith bob@email.com 555-111-1234 Assuming ...
0
votes
2answers
21 views

Searching Multiple Columns From Single Source Sheet in Google Sheets

I have a Sheet where I have all of my data entered. I have been able to filter the information to other sheets no issues when only searching in one column. I am having issues when trying to search 2 ...
0
votes
1answer
15 views

How can I use a filter to show everything except cells containing specific words?

I'm importing a Google Calendar (ics link in A2 that I'm importing with an IMPORTDATA function) and it has system dates related to 1970 that I obviously want to filter. How can I specify to show me ...
0
votes
2answers
44 views

How to stop banding from re-appearing on Google Sheet

I'm using banding to highlight some areas, with a section in between with a fixed colour. It should look like this. Sometimes, when I enter a number in the blue area in the middle, the banding ...
0
votes
1answer
20 views

I need to extract a data validated cell content and use it as a part of a new formula

I would like to give the user the option to choose between DAYS360() and NETWORKDAYS(). For that, I thought about adding these 2 to a data validation cell. Afterwards, I would like to be able to add ...
0
votes
1answer
26 views

How to use multiple criteria with Regexreplace in Google sheet?

I am trying to correct the number/expression of room numbers that are entered by users with the following: 1- If the number contains ( / or \ or _ ), it will be replaced with "-". 2- If the ...
0
votes
0answers
12 views

how to remove contact from Google contacts when email removed from google sheet using apps script

I have an Apps script when run: reads Google sheet, adds new contact/s ( name and email) into Google Contacts, a duplicate is prevented to be entered into Google contacts as well. However, I want when ...
0
votes
0answers
14 views

How to use Divide function actively? Ex: If I change the value of divider, how do I keep this up to date?

How do I make the values of divided, sum etc. active? I can't figure this out. If I use the =DIVIDE(etc). the values in these cells stay the same if I change the values of divided or divider.
0
votes
0answers
16 views

Google Sheet: calculation/math inaccuracy [duplicate]

I found weird math error. I was doing calculation that comes down to this: =TRUNC(253-0.81*300) This comes back as 9, but should be 10. For some reason, when you increase the visible decimal places, ...
0
votes
1answer
32 views

Plotting gigantic values + smaller values on the same Google Sheets chart?

I'm trying to plot a chart that includes one data set in billions, another in millions, and a third in thousands. What is the most efficient/elegant way to do this using google sheets without the ...
0
votes
1answer
10 views

typing 1 to 9 on keyboard does not trigger editing cell in Google spreadsheet

When on a iPad in Google Sheets and typing 1,2,3,4,5,6,7,8 or 9 does not trigger the editing of a cell. 0 and all other characters do. Is this a new feature/bug in google sheets or can I change a ...
0
votes
1answer
50 views

Add checkboxes in a column automatically when another column contains a value (or not) in Google Sheets

I need to automatically add checkboxes in column F but only when column G contains a value in the same row. Conversely, I want a checkbox to disappear or be disabled when the cell in column G in the ...
0
votes
1answer
19 views

How to change IFS function to switch in Google sheets? [duplicate]

I have this formula =ifs(OR(A1="January",$A$1="March",$A$1="May"),$A10,OR($A$1="April",$A$1="June"),$B11,OR($A$1="February"),$C10) I would ...
0
votes
1answer
17 views

Google sheets range of sheets?

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
-1
votes
1answer
11 views

Problems when sorting a Google sheet that someone else is editing

In the past, when two people were editing the same Google sheet, if person A attempted to do a sort they would get a "heads-up" warning that A's sort was going to affect person B. We now don'...
0
votes
0answers
16 views

Autofill Google Sheets formula incrementally

I'm looking to pull data from several separate sheets into one overview to have a snapshot picture of a range of booking in my venue. I'd like to look at the venue booking per space for any given day, ...
0
votes
0answers
16 views

Action paln formula for google sheet

I need to do a plan for my team. Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call, break, and task. I have to add a rule so that an agent per day ...
0
votes
1answer
11 views

Way to link to first (leftmost) tab in Google Sheet?

I have a Chrome bookmark pointing to a time tracking spreadsheet I have created. Every month I add a new tab for the new month, which becomes the first (leftmost) tab. Unfortunately this causes my ...
0
votes
0answers
9 views

Import data from Investing.com or other sites into spreadsheet not possible anymore? [duplicate]

I wanted to import from the section of historic data high/low prices in weekly ranges. For example from this site: https://www.investing.com/indices/us-30-historical-data I tried =INDEX(IMPORTHTML(&...
0
votes
1answer
19 views

Formula for Google Sheet

I need to do a plan for my team. Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call break, and task. It is a team of 15 people, I can't find any ...
0
votes
1answer
11 views

How to find closest similar value above current value, and return a different column on the same row

I'm looking for a way to find the closest similar value(in distance) above the current value(column B), and return the column next to that found value(column C) in column D. A sort of vlookup where it ...
0
votes
0answers
7 views

Show label when hovering over point in diagram

My chart is quite crowded but I need to be able to look up the manual labels assigned to each point. Is there a way to make Google spreadsheet show the manual label upon hovering over the point with ...
0
votes
1answer
21 views

Get the last three values of column in Google Sheets

I need to create a report that pulls the last 3 cell values in a column. This will need to be in three separate cells. Column C will contain data which will be added to daily. D2 will contain the ...
0
votes
0answers
22 views

How do you color a cell if no other cell references it? [duplicate]

I am making a spreadsheet with a bunch of variables I want to change and some formulas that use those variables. I want to use conditional formatting (probably?) to color any cells which are "...
0
votes
0answers
12 views

Differences between copy-pasting from Excel/Calc/Google Sheets to Whatsapp [closed]

When I copy an excel table (or libreoffice calc) and paste it on whatsapp web I have a photo but when I paste a Google Sheets table I get the plain text on whatsapp web, as well as the images below ...
0
votes
1answer
56 views

Count of weekdays and the time duration per day

I have a column that has 34 records of Week Day, Month/Day, and Times. I am looking for two formulas that I can use in a table that will give me the count of weekdays and the time duration per day. ...
0
votes
1answer
15 views

Depending on Sheet being edited, get a cell value and trigger OnEdit update

This code works but not as intended. The workbook has several tabs (Sheets) and if the user is editing Tab1, then and only then a trigger OnEdit should execute. If the user is editing some other tab (...
-1
votes
0answers
14 views

Create a formula in Sheet

I want to make a formula to check if the text of a column matches a text added in another in undefined range and without ordering and give as an answer yes or no. Example: In column A: Red wine White ...
0
votes
0answers
19 views

Max sum held over a month

I need help with a formula that needs to calculate the max sum held at one time for each month. In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
0
votes
0answers
12 views

While using INDEX and REGEXEXTRACT, how can I return a blank cell instead of 0 [duplicate]

While using INDEX and REGEXEXTRACT, how can I change this formula to return a blank cell (instead of 0) when all of the cells in that section are blank?
-1
votes
1answer
22 views

Autofill Google Form for Sent Emails

Problem: I need to keep a comms log for emails sent by my teams to other teams. We are running a multiyear project that has over 150 customers. We often escalate and get responses from directors ...