Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

1
vote
3answers
41 views

Filter cell data using Query/Importdata

I am attempting to grab data from a cell, by using additional data in the cell above it to make sure its correct. Here is the data, each line is put into a cell by the formula. <total_count>6&...
0
votes
1answer
12 views

Replace value in field automatically (auto-correct?)

I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098. Is this possible?
0
votes
1answer
14 views

Collates the number of depots that don't have manifests and trunk headers

Trying to do a work project that collates the number of depots that don't have manifests and trunk headers. Column A: Depot Numbers Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
0
votes
1answer
9 views

Data validation if no then disable cell else drop-down lists

I'm trying to do a drop-down list IF another cell doesn't equal "no" So I have two columns: one with a drop-down list containing "yes" and "no" and in the other column I want to do a drop-down list ...
0
votes
2answers
9 views

Update each cell in column from upper right adjacent cell

How can I make the start mileage in each cell update from the end mileage in the upper right adjacent cell? Preferably using something like a single array formula that can be updated. I think I'm ...
0
votes
0answers
22 views

How to produce and then export 100+ column charts based on one data set [closed]

I've got to produce and export c.120 graphs from one set of data. Column A are a list of names (student A, student B etc), and each name has 12 'scores' (test 1, test 2 etc). I want to represent those ...
0
votes
1answer
13 views

INDEX + MATCH to return the greatest of all matching values

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order. Example: When was [Roberto]'s most ...
1
vote
2answers
9 views

How do I automatically enter sequential numbers without using the mouse in Google Sheets?

I would like to create a series of number in Google Sheets, without dragging with the mouse. When using the Shortcut Cmd+R (Fill Right) it just copies the value of the first cell. Initial State: ...
1
vote
1answer
36 views

Goggle Sheets IMPORTXML/REGEXEXTRACT out of date

Alright, so I found my basic answer from these posts, 2-3 years ago. Extract data from Steam Import total hours on Steam Steam has updated its website since then, so I've messed around with it and ...
1
vote
2answers
20 views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
0
votes
1answer
11 views

Google Sheets Conditional Formatting for expiration dates

I need help! I need to create conditional formatting for expiration dates: Cells should stay green Cells should turn yellow when 1 month of exp. date Cells should turn red once expired
0
votes
2answers
9 views

Conditionally format cells that not match with range from Sheet2

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my jira. Now I am trying to compare Column F from sheet1 and sheet2 to confirm if they are ...
1
vote
2answers
14 views

Change cell colour based on another cell containing text (day name)

I am new to Google Sheets and trying to create a custom formula in conditional formatting to make the fill colour of a cell change based on another cell containing a specific text. I want to change ...
0
votes
0answers
9 views

Take a Note from a cell

How do I get a Note that I put on a cell from another cell? I need that a range of cell share the same Note. It is possible?
1
vote
1answer
17 views

How to filter only the highest value for each date based in a criteria?

I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red". My sheet: date entries code 08/04/2019 1 red 08/04/...
0
votes
1answer
16 views

Combine multiple sheets in one master file

I am applying the below formula where I want to combine the live data from multiple sheets to one consolidate sheet in horizontal format & here my header is same... but I am getting an error as: ...
0
votes
1answer
9 views

How do I arrange raw data in Google spreadsheet in group way?

I have a list of orders, like this: Paid $28.00 | 2019-04-08 15:14:20 Unpaid $00.00 | 2019-04-08 15:13:48 Unpaid $00.00 | 2019-04-08 15:12:58 Unpaid $00.00 | 2019-04-08 15:12:10 Paid $18.00 |...
0
votes
0answers
16 views

How to select activated sheets cells in Google script, rather than sheet by name

I need my script to talk to the "current sheet" rather than sheet by name. Code as is, selects cells from sheet 2019 but I want the script to copy the values from the current sheet, and paste into ...
0
votes
1answer
5 views

How to split a cell into key/value rows?

Apple,3,Orange,4,Mango,10 How can I split this cell into key/value rows? Apple (a1), 3(a2) Orange (b1), 4(b2) If I just call split(), I get: Apple 3 Orange 4
0
votes
1answer
10 views

Outputting a cell x number of times in an array format

Using Google Sheets: Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1. I've created a dummy file to show what ...
0
votes
0answers
6 views

In Google Sheets, why won't a cell update unless I give it a “kick”?

TL;DR A cell in one sheet in a Google Sheets workbook refuses to update when it should -- i.e. when relevant changes occur elsewhere in the document -- unless and until a recalculation is forced, for ...
0
votes
0answers
11 views

Calculated field in google sheet pivot table

I encounter a problem when trying to make a simple formula in calculated field. I can't reference a column with a space on its name. For example, if my column is "Column A" : formula : "='Column A'" ...
0
votes
1answer
8 views

How can a forumula be converterd to array formula in Google Sheets?

How can you convert these to array formula? =IF(COUNTIF($I:$I, $A:A)=0, "No", "Yes") and =CONCATENATE(C3," ",D3)
-1
votes
1answer
13 views

How can I search an argument in GSheets to return TRUE if the argument references another cell?

Hello! In some cells in the True Price Column, I applied a discount by referencing the cell Conv!C2. For example, the argument in D13 is =MROUND((D8 * 2) * Conv!C2, 5) where (D8 * 2) is me calculating ...
1
vote
1answer
16 views

How to have a table that updates across multiple sheets in both directions in Google Sheets?

I have two sheets, and a table that I want to be displayed on both sheets. I can use =('Sheet_name'!A1) to create a one-way dependency. However, is it possible to create a table that updates both ...
0
votes
1answer
11 views

Value transformation in Google Sheets

I'm working with a table of data as following: User ID: Event name: 111 Event 1 222 Event 2 333 Event 3 111 Event 4 444 Event 5 111 ...
0
votes
1answer
10 views

How to disable new row when Google Form collects responses for Google Sheet?

When Google Forms collects responses, it creates an entirely new row in the Google Sheet which I don't want to happen because I am using a VLOOKUP for another sheet and it's not grabbing the data I ...
0
votes
0answers
12 views

Trigger script in Google sheets with Zapier action

Is it possible to set an action in Zapier (like webhook) to trigger a Google sheets script? What's the right way to set this up? This has been driving me crazy so I hope there is someone out there ...
0
votes
1answer
5 views

Creating a formula based on TODAY's day

I have column A (list of stock prices at close of day) A1=4/1/2019 | stock price A2=4/2/2019 | stock price A3=4/3/2019 | stock price each day will be added to the sheet automatically I want to take ...
1
vote
1answer
21 views

How to get the largest initial values of multiplied columns?

I'm tracking performance for some athletes and we need a way to see their "Best" workout numbers meaning the highest numbers of (rep × weight). The table looks like this: The reps and the weight are ...
0
votes
1answer
25 views

How can I attach a file to an email using Google script?

I'm using Google Script to send emails, but I'm not sure how to add attachments (multiple attachments). I am also not sure where to add the code for it either. The file is on Google Drive and I need ...
1
vote
2answers
16 views

Google Sheets - Convert entire column of prices by 50%

I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below). I need to convert this price list to wholesale. All the products wholesale ...
0
votes
1answer
14 views

How do I keep leading zeros in Google Sheets after split?

This is my data: 43558#DEYINGCHEN20190403#097855111593#Logitech MX Master Wireless Mouse (910-004337) Then I used split(#). Result: The result I want is:09785111593 This cell I have formatted into ...
0
votes
0answers
12 views

Sort sheet1 according changes in sheet2

I'd like to sort range in sheetN2 if data in sheetN1 are changed, but the script doesn't work. Could anyone tell me where the problem is? function onEdit(event){ var ss = event....
0
votes
0answers
7 views

How to maintain on-screen character size when printing in Google Sheets?

I have put a "Special Character" from Docs into a cell and used a large font size to make a large tick box for a printed form (I have multiple tick boxes in the same cell and so cannot use the inbuilt ...
1
vote
1answer
30 views

Subtract 8 hours from time, if time is more 8 hours

I am trying to create a Timesheet using Google Sheet but this formula =IF((F8>8),(F8-8),"0") returns zero even on cells with Total Hours greater than 8 I have set both Formats to Duration
1
vote
2answers
135 views

Image in a cell with a hyperlink

I need a cell that contains an image to also contain a hyperlink. Following formula is the only one I found but doesn't work (error analyzing formula) =HYPERLINK("URL", image("URL")) Following ...
0
votes
1answer
16 views

ArrayFormula date vlookup -30days in first 30 rows of Google sheets

I am trying to use an array in Google Forms Response sheet for a time sheet. I want to have a rolling 30 day total for hours worked. The first 30 days is not able to lookup 30days prior. ={"Previous-...
1
vote
1answer
23 views

Conditional Formatting using Google Sheets

So I'm trying to build out a simple campaign tracker and I've been running into a few issues with the conditional formatting on google sheets. I need the cells on column C to change color and format ...
1
vote
1answer
6 views

How can I encode HTML literals for output within the Sheets Query function?

I have a query in a spreadsheet. What I need to do is output a bit of simple HTML text between each column being pulled out of another tab. Sample code looks like this: =Query(Official!A7:P617, "...
1
vote
1answer
14 views

How to calculate a subtotal based on multiple columns in Google Sheets

I want to create a dynamic formula, which will calculate the sum of column C, subtracted by the sum of column D, between the range of the current new subtotal and the previous subtotal, and adding the ...
1
vote
2answers
9 views

How do I detect duplicates in two worksheets?

I have 1 Google Sheet with 2 worksheets: Worksheet "1" has 1 column A with 5 lines of data (AAA, AAB, AAC, AAD, AAE) Worksheet "2" has 1 column A with 5 lines of data (AAA, BBB, CCC, DDD, EEE) I ...
0
votes
1answer
13 views

How to turn checkbox with IF formula?

I have a cell with an "end date" and I have a checkbox under an "Inactive" labelled column. I would like that checkbox to turn on when today's date is after the end date. I can't seem to add a ...
0
votes
1answer
16 views

Importrange doesn't import data from extensive sheets

The formula works on lower column number that I need to get data from but nothing is happening when I need to import data from columns with a higher number. Following formula works (data imported ...
1
vote
1answer
10 views

Rounding in an array equation?

I'm hoping someone can help me out... Is there a way to round: =ARRAYFORMULA( (If(ISBLANK(A2:A),"", (H2:H*10^-3)/((E2:E*10^-3)*(F2:F*10^-3)*(G2:G*10^-3))))) to two decimal places in the equation ...
1
vote
0answers
18 views

How can I disable the authorization needed to open Google Sheets Macros?

Whenever I try to open any macro enabled sheets it requires authorization, even though it's a first party application. Is there any way to stop Sheets from asking it or give permissions to everyone I ...
1
vote
1answer
18 views

Using a single cell serving as an ID source for formulas to import data from

I have my master Google sheet and I need a weekly updated Excel report to be imported into that sheet. Right now I use =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; IMPORTRANGE("XYZ"; "'Overview'!A4:FZ"); 10; ...
0
votes
3answers
23 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
0
votes
0answers
7 views

Delete cells by value in Google Sheets

I want to delete every cell in a spreadsheet that contains the value 0, and shift the cells below it up. How can I do that?
1
vote
2answers
20 views

Return values to another sheet if a cell is not empty, otherwise skip to next cell

I have one sheet with a list of phone extensions in which some have names associated with them and some do not. I am trying to use a function to display one the extensions with names onto another ...