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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
1answer
34 views

Apply protection to multiple sheets in a Google Spreadsheet

Ive got a Google Spreadsheet with 50+ data tabs, and 5 Summary tabs at the end. The 50+ data tabs all have the same column headers. I want to apply protection to the all these data "data tabs" to ...
0
votes
1answer
30 views

How can I comment out parts of a formula in a Google Drive Spreadsheet?

How can I comment out parts of a formula in a Google Drive Spreadsheet? In PHP you can comment a line of code using a # what is the syntax to do the same in a Google Spreadsheet? I want to use this ...
0
votes
0answers
27 views

Google Sheets Array formula

=ArrayFormula(IF(ISBLANK(Input!$B4:$B)," ",(INDEX(Input!A4:A, SMALL(IF(COUNTIF($A$3,Input!$B4:$B), MATCH(ROW(Input!$B4:$B), ROW(Input!$B4:$B)), ""), ROWS(A6)), COLUMNS(Input!B4:B))))) The result is ...
1
vote
2answers
24 views

Select a value from range

I'm trying to put the category number (1..5) into E3 based on the value in F3 being in between the From (B) and To (C) range. For instance in the example below F3 contains 19.3 which is between 12.5 ...
1
vote
1answer
17 views

Countunique if cell in same row in other column is not empty

Is there a way to calculate the amount of uniques you have in a column with respect to another value not beeing empty. For example: A 3 B X 5 Y A X 3 Y This should then result in the answer ...
1
vote
0answers
7 views

How can I include ! in named ranges or change the cells in a named range easier in Google Sheets?

I'm not 100% sure what i'm doing here as I'm pretty new to this, however what I am trying to do is have ranges with the same name on different sheets where I get "SheetX!Range" as the name of the ...
0
votes
0answers
16 views

Google Sheets: Generating a table with vertically merged cells based on a categories and sub-cats

Given a table of Categories: ---------------------------------- Category | Type | SubType | ---------------------------------- cat1 | T1 | S1 | cat2 | T2 | S1 | cat4 | ...
0
votes
1answer
11 views

How do you reference the entire google spreadsheet into another?

So, I have two workbooks, one with multiple sheets(W1) and one empty(W2) I want the empty one (W2) to have all the data (all strings, chars, and SHEETS) that (W1) has. IMPORTRANGE only takes data ...
0
votes
0answers
27 views

Loading Data Error Query with Importrange Gsheet

I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
1
vote
1answer
20 views

Color or group the same kind of data in a Google Sheet graph

I have the following data: | Test Combination | C-Score | Memory | Reasoning | Verbal | |------------------|---------|--------|-----------|--------| | ABCD | 13.70 | 10.51 | 13.04 |...
0
votes
1answer
28 views

Google Sheet Humongous Bottleneck Caused by this FILTER function. How to fix?

I have identified by trial and error that this one FILTER function is causing a massive "Calculating Formulas..." delay in my Google Sheet. This is the ONLY cause of the delay I have tested it ...
0
votes
0answers
16 views

How can I make a auto sorter that has inputs and color changing?

I really wanna some sort of Google sheet auto sorter, lets say for example I have some information like a person name, and I want to input this data into the google sheet into lets say Cell A1, and ...
0
votes
1answer
26 views

Query with sums and 2 columns on the left

I have problem with the query: =Query(Raw_Data!J$2:$BL,"Select J, Sum(U),Sum(V),Sum(W),Sum(X),Sum(Y),Sum(Z),Sum(AA),Sum(AB),Sum(AC),Sum(AD),Sum(AE),Sum(AF) where J contains 'CUS' and (AL='1-Revenues'...
0
votes
1answer
17 views

Multiple IF(AND) statements to determine Pass/Fail

I'm using Google sheets to try to generate a Pass/Fail in a cell based on two different parameters; If (R2) is less than 85% AND (I2) equals "T1","T2, or "T3" I need a fail generated. similarly, for ...
0
votes
0answers
10 views

Convert records with comma-separated values into unique records for each value [duplicate]

I've got a Google Sheets spreadsheet with records of individuals along with a column for comma-delimited phone numbers for each individual. I have been pulling my hair out trying to generate a two-...
2
votes
1answer
79 views

How to extract multiple paths from a single string of URLs in Google Sheets?

How can I get a subrange of second to last element in a formula under Google Sheets? I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
0
votes
1answer
14 views

Counting with Query

I am using queries, in order to create a dashboard, =query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1) I am having issues with my query function. I want to count how ...
0
votes
1answer
22 views

In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action

In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
0
votes
0answers
12 views

Google Sheets: updating cells that has been fetched by IMPORTRANGE() [duplicate]

I've got two Google Spreadsheets. Spreadsheet B fetches data from Spreadsheet A using IMPORTRANGE: =IMPORTRANGE("url", "tab!A1:JB19") What I want to achieve is to be able to write over fetched data ...
1
vote
1answer
7 views

Google Spreadsheet sum when filtering

I have spreadsheet that looks like this: Now when I filter on a company I want the total to display the sum of the whole of B column. Since I am very new to spreadsheets I was hoping someone could ...
0
votes
1answer
20 views

How many 'importranges' are being used in a selected Google Sheet

How to find out how many importranges are being used in a single Google Sheet?
2
votes
3answers
41 views

How do I get the address of a range with a formula?

Let's say I have a range called WidgetTypes in cells C5:M5. I want my formula in cell A1 to return C5:M5 or $C$5:$M$5. What formula goes into cell A1? cell("address",WidgetTypes) returns $C$5 but I'...
2
votes
1answer
157 views

How can I use a formula to split a text into individual characters, regardless of length?

I have a list of words in Japanese. I want to write a formula that will input the word, e.g. ひらがな, and output it as an array of however many characters: ひ + ら + が + な, no matter the length of the word....
0
votes
0answers
26 views

Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
0
votes
1answer
36 views

Help to modify code please

I would like to use this code : function onEdit(e) { if(e.range.rowStart === 1 && e.range.columnStart === 1) { e.range.offset(0,1).clearContent(); } } but only on a tab called '...
0
votes
1answer
42 views

Auto numbering Google sheets

I want to have auto numbering dependent on cell drop down box in helper column so i can sorted out. If someone pick Start in A2 to have number 1 in B2. Then if they skip one or two and chose Start ...
1
vote
1answer
22 views

How to calculate the following mathematical expression in Google Sheets?

I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
2
votes
1answer
20 views

Sumif and Sumifs with open ranges gives #ERROR in one sheet, correct result in other

Here's my test sheet: Localization is USA with timezone GMT+1 I've tried substituting ',' with ';' to no avail. https://docs.google.com/spreadsheets/d/1pZku_X6b49uMhKivnv3N1uv6sekYrhY12IlUct_Zudc/...
2
votes
1answer
245 views

“Invalid Input, must be item on specified list” Google sheets error

I am super new to anything coding and google sheets. All of what I've done was was trying to follow the directions of another question I saw on this site. However, as expected, nothing is working! ...
1
vote
1answer
28 views

“Calculating formulas” in Google Sheet disaster

I have a worksheet with 13 sheets. the largest one has 2000 rows. I have already deleted all the possible blank rows i could. I have also read all the guides on how to make a Google Sheet faster and ...
0
votes
2answers
13 views

Opposite of SWITCH function? (i.e. enums in Google Sheets)

In programming, I could use a switch/case statement to do something like this: func animalNameFromType(animalType: Int) { switch animalType { case 1: return "dog" case 2: return "cat" ...
1
vote
0answers
15 views

How to have font size adapt itself to cell size?

I have a document where I use one sheet for presentation purposes. It's like a window/viewer of the content of the main sheet (the cell contents are all references to the main sheet). I built myself ...
1
vote
0answers
65 views

Insert image to spreadsheet from google drive folder, png image dose not show, why?

Update It is now working,i dont know if google fix it or what. But if i use the image function and use the script below to find the id from the folder it works. Here is my final text if someone would ...
1
vote
1answer
35 views

How to treat data pasted into Google Sheets as number instead of text and sort accordingly?

I pasted a table into Google Sheets (part of Google Docs), and one column is 1/234 1/102 1/1222 1/83 and when sorted according to this column, the column data is treated as "text" and sorted ...
0
votes
0answers
18 views

How do I edit cells in Google Sheets, after filtering or importing a range [duplicate]

when I import a range, in Google Sheets, or I use the formula Filter(), I get an array of data. I would want to then be able to edit the imported/filtered data, is that doable? of course, without ...
2
votes
1answer
18 views

Add item to a google sheet's cell from url?

Is it possible to pass a URL to a google sheet and have the sheet append a new cell with content taken from a parameter in the URL?? Use case: In KDE (and other environment as well, perhaps), most ...
1
vote
1answer
16 views

Conditional formatting Google sheets

I want A1 on Sheet1 to change color based on specific text in cell B1 on Sheet2 or even different Book...is that even possible with custum formula? Tnx in advance P.S. =ISText(indirect("Sheet2!B1")) ...
0
votes
0answers
20 views

Linked Google Sheet requires authorization although public

I have made a Google Sheet that links to another Google Sheet, using IMPORTRANGE(). Although the linked sheet has Public accessibility, I am still required to authorize access to the linked sheet. Why ...
0
votes
1answer
31 views

Reverse Shortened URL to Get the Original URL Before the Shortening in Google Sheets

I have a sheet where I collect tweets with links based on a search query. Twitter, however, is handing me for each tweet their short URL format (t.co) rather than the original URL that was shared. Is ...
0
votes
1answer
67 views

How do I set up this checkbox formula in google sheets? I'm getting #N/A error

I have a list of players in my league and next to each name is a checkbox to show if they are active or not. I set up a formula that checks each of the 12 weeks of game play to see if a player ...
0
votes
1answer
11 views

How do I advance the column instead of the row when I copy/paste a formula?

So pretty simple thing I am doing, pulling data from a form and putting it in a template. =('Form Responses 1'!B3) is what I am doing, which works fine for my needs, however when I copy and past ...
2
votes
1answer
42 views

Getting #DIV/0 error in Google Sheets for AVERAGE, SUM, COUNT, etc on column generated by ARRAYFORMULA

I am trying to get the average value of a column (column C "Profit Margin" in the screenshot) in Google Sheets. For some reason, I get a #DIV/0 error when trying to use any sort of range functions ...
1
vote
2answers
24 views

How do I get this formula to increment by 1 per 25?

I need my formula to increase by "1" for every "25". I wrote the following formula, but it does not work: =SUM( IF(O11<25,0, IF(O11<50>21,1, IF(O11<75>51,2, IF(O11<100>76,3, IF(...
0
votes
1answer
26 views

Is there a better way or more efficient way to write this IF statement? [duplicate]

I need a way to get the return number to increase by "1" after every increment of "10". Is there a more efficient way to do this other than: =SUM( IF(AC11<10,0, IF(AC11<20,1, IF(AC11<30,2, ...
1
vote
1answer
38 views

Can I use “countIFS” with a filter?

https://docs.google.com/spreadsheets/d/1PlPmShZ4SnkxAqBTg5xY_LHaNokGaFli4Wc7MjV_Jqo/edit?usp=sharing I want to count how many paid is for city MB and then how many paid is for city UB. I tried to ...
2
votes
1answer
41 views

Reference formulas from one sheet into multiple sheets

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced. For example, if I have =...
1
vote
0answers
14 views

Google Sheets: move a chart and still have it update from original data/arrays?

Can you move or copy a chart in Google Sheets into another EXISTING sheet and have it still update form the original data arrays? I know you can move a chart into its own sheet, but I'm looking to ...
1
vote
1answer
44 views

How to show the current day of the week in Google Sheets

I have a Google Sheets that works as my To-Do list, and I want to make a cell that displays the current day of the week in text and updates itself every day. For example, Monday or Friday.
1
vote
2answers
44 views

Combine columns where there is only one data cell per row in Google Sheets

I have Google Sheets data that looks like this: A | B | C ----------- 1 | a | | 2 | | b | 3 | c | | 4 | | | d 5 | | e | 6 | f | | 7 | | | g 8 | | h | I need to ...
-1
votes
2answers
31 views

Linkable preview of Google Docs (Sheets, Slides, etc.)?

Is there a way to get a live preview image (an image, similar to the content of the document that updates without manual intervention) of a Google Sheet (or other apps in the Google suite) to link to ...