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
2answers
69 views

Need questions related to initial questions?

I'm trying to make a google form that my friends and I can use to enter our board game scores into an excel sheet, which then calculates our overall and per game scores... Does anyone know how/if I ...
0
votes
1answer
55 views

how to use MAX in a query and use the result as a number in Google Sheets?

If I write a formula using query and limit it to one result, I can then use it as a number, for instance =1+QUERY(B13:F18,"SELECT C WHERE (B='Banana') LIMIT 1") But if I want to use the ...
0
votes
2answers
933 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
1answer
20 views

How do I correct the automatic borders that are scripted into this Google Sheet?

I have this Google Sheet document that already has functionality built in which applies automatic borders. I don't know how it is doing this. I've tried looking in the Script Editor, but I don't see ...
0
votes
1answer
11 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 ...
1
vote
2answers
25 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 ...
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
1answer
44 views

Best way to change dates in Google Sheets in all the tabs?

I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
1
vote
1answer
944 views

Temporarily disable autosave in Google Sheets

I use autohotkey scripts for a lot of things in Google Sheets. It's basically scripted, automated keyboard input. A script may look something like this: Loop, 4 { Loop, 7 { Loop, %...
0
votes
1answer
10k views

How can you *turn off* the new "Show Edit History" feature in Google Sheets?

I turned on "Show Edit History" for one cell in a Google Sheet and now I want to turn it off for that one cell. I can't see any way to do so. If I right-click on the cell I see "Show Edit History" ...
0
votes
1answer
16 views

Counting a cell if another cell has certain strings

Have a cell which will either have W, L or BB in it. I want to add the cell in O6 if the N6 cell contains any of the strings mentioned below what is the code for this? below is what I currently have ...
4
votes
2answers
4k views

Count number of consecutive entries with Arrayformula in Google Sheets

I want to count the number of consecutive times the same value appears until the given row, until another value appears then start counting from zero again. And I want to use an arrayformula so I don’...
0
votes
1answer
26 views

Convert Script into HTML and have links embedded

I have the below script and want to have the URL links embedded instead of the whole link appearing on the automatic emails, but don't know how to convert this script into an htmlbody script to have ...
1
vote
2answers
56 views

Count how many times a certain word comes before and after another in a list

I am trying to evaluate some information from an animal studying exercise using Google Sheets. I'd like to know how many times the animal: forages then allogrooms sleeps then allogrooms etc. Also ...
1
vote
1answer
46 views

Aggregate data to show text in one cell

I need to put together multiple entries into one where the results is text and has to be in one cell. From this to this
0
votes
1answer
88 views

The cell is filled with $0,00 but IF(Cell >0;True;False) return True

In Google Sheets, when I compare a Cell filled with $0,00 using: CELL [$0,00] IF(Cell > 0; true; false). It returns true, but Cell is = 0. It should return false. It seems to be related to ...
0
votes
0answers
9 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 ...
0
votes
1answer
43 views

Google Sheets Conditional Formatting Cycle

In Google Sheets I would like to color a cell depending on if the cycle is on or off. The cycle is for 10 day the cycle is ON then for 5 days the cycle is OFF then repeat. So if I start the cycle on ...
0
votes
1answer
46 views

Add fields and update array and scripts

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that. Here is what it should look like: The URL is the link for the form. Here is ...
0
votes
0answers
26 views

Find the duration in weeks between grouped events using the 1st of each event

I have a data set where i'd like to have the duration between each grouping of events by comparing only the first (earliest) event in the grouping as a google sheet arrayformula. The output should be ...
0
votes
1answer
31 views

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ]

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ] , like a new line. I found a formula but what it does is create a range of columns first. But I can't do it because there ...
0
votes
1answer
18 views

Merged cells not preserved when copying and pasting

I wrote a script to copy and paste a block of cells from one sheet to another in Google Sheets, and everything is working fine except the merged cells are no longer merged after the cells are pasted. ...
1
vote
0answers
19 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
0answers
10 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
1answer
24 views

How do I chart timeseries data organized by one of the columns?

Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done. I want a chart with time as the X ...
2
votes
1answer
35 views

Sums with Same Value - Follow-up Question

This is a follow-up to these questions and information. Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a ...
0
votes
1answer
21 views

Is there a way to embed a specific cell from a Google Sheets to Google Docs?

I want to fill in a sentence with blank spaces on Google Docs from information that I can provide from Google Sheets. Is this possible? "For my student number X = [student_number], the required ...
1
vote
1answer
14 views

Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
0
votes
0answers
15 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 ...
2
votes
1answer
30 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
0
votes
1answer
41 views

How does one automatically update rows/data with a Slicer filter when a spreadsheet is edited?

How does one automatically update a Slicer when data is changed on a spreadsheet? I would like it so that when I change cells and data on the spreadsheet, the Slicer filter will automatically update (...
1
vote
1answer
116 views

Can I record the users within a Google sheet?

I'd like to have an auto-generated list of users with timestamps within a spreadsheet, tabs, or if possible specific cells. Ideally, once a cell has been altered, I'd like another cell to record the ...
0
votes
1answer
2k views

How to organize and add multiple tags to a list?

I'm trying to create a sort of "goal bank" and I'd like to organize it so that you can search for goals based on various categories (i.e. diagnoses [autism, mental health, etc.], domains [social ...
0
votes
1answer
19 views

In Google Sheets, how do you use their Fill Down (dragging formula thing) without copying cell styles / formatting?

Cannot find this anywhere I look. Lot of SEO spam on Google Sheets. I have a table that has a ton of different styles: Header small font BIG FONT bold font italic font The "small font" ...
0
votes
1answer
2k views

How to use calculated fields to pull up "Grand Total" in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
0
votes
0answers
15 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?
2
votes
1answer
28 views
1
vote
1answer
98 views

How do I copy formatted formula results in Google Sheets?

I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I ...
0
votes
1answer
14 views

Protect identical ranges across multiple Sheets

I currently have a multitude of docs each containing 30-60+ sheets. Each sheet is formatted identically with the same headers/column labels. In doing research I found its not possible to mass protect ...
0
votes
1answer
1k views

Auto-populate cells based on previous cell values in Google Sheets

I have two columns with information. Values in Column B have been manually added to match the values in Column A. Is it possible to have a function that auto-populates column B with the right values ...
1
vote
1answer
388 views

SUMPRODUCT inside ARRAYFORMULA in Google Sheets

I have a SUMPRODUCT equation that works fine on one row: =SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10)) However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically ...
7
votes
3answers
14k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
0
votes
1answer
22 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
11 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" ...
-1
votes
0answers
17 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 ...
40
votes
10answers
86k views

How to specify the entire sheet as range in Google Sheets?

The best workaround I have found so far is: worksheet_name!$A$1:$YY but ideally I would like to be able to simply write, e.g.: worksheet_name! So, does anyone know: is there a syntax for ...
0
votes
1answer
22 views

How to add to each element of an array

I'm trying to take each element of an array that's generated from a SPLIT function and do some math on them, specifically, I want to add 3 to each element. Cell starts off with this: 1,2,3,4,5 SPLIT ...
17
votes
3answers
36k views

How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?

In Google Sheets, I have the following data: | A | B | C | ... | N | TOTALS | 1 | 123| 425| 324| ... | 234| =SUM(A1:N1) | 2 | 123| 425| 324| ... | 234| =SUM(A2:N2) |...
3
votes
2answers
12k views

Keep multiple sheets synchronised (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
0
votes
0answers
7 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 ...

1
2 3 4 5
146