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

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.

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
139 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
17 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
24 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
14 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
19 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 ...
0
votes
0answers
14 views

Google Sheets - Incrementally referencing rows, so that the value of the previous rows are summed?

How do I incrementally reference rows (e.g for N4, I can reference cells L4 and M4) and when copied across to the right, the next cell (O4) will sum L4, M4 and N4)? e.g. (= IFS (L4+M4+N4 >=K1, 0, L4+...
0
votes
1answer
29 views

Summing hours spent on a subject in Google Sheets' “Assignment Tracker”

Hi everyone! I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically ...
0
votes
1answer
23 views

Linking a dynamic inventory price list to another file in Google Sheets

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
-1
votes
1answer
50 views

Delete multiple sheet tabs in Google Sheets

In a Google Sheets spreadsheet (Excel terminology = workbook) is it possible to delete multiple tabs (Excel terminology = worksheet) in one go without having to right click each one and delete it.
1
vote
2answers
48 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
0
votes
1answer
28 views

What is the updated method of “CalendarApp.openByName”

We use a that gather the registration from Google Form and add them in our Agenda, our script uses the method "CalendarApp.openByName" to add the name automatically inside a Cell. It says the method ...
0
votes
1answer
12 views

Google Sheets adding weird number in 11th decimal place

Can anyone explain this? I have 18 hardcoded numbers - that is, typed in, not as a result of a formula. [B1 thru B18] I have a cell [D1] that sums those 18 numbers. I have eleven other hardcoded ...
0
votes
2answers
21 views

Remember each user's last cell cursor location?

Is it possible to have Google Sheets remember each user's either last cursor location or last edited cell or preferably the value of the cell in Row2 that matches the current date? (In Row1 I have the ...
0
votes
1answer
8 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
0
votes
1answer
29 views

Auto sorting / copying data based on inputted data?

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
0
votes
2answers
31 views

How to create a function to “Save as” CSV file?

Here is my situation, I have Google sheet with just one sheet, so I allow the user to enter data there by just filling each row, ok, I have created a little script to add and extra custom menu, just ...
0
votes
1answer
14 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
0answers
40 views

Validation of cell values

I have a problem (like everyone here, obviosly). In the sheet in the column E from row 2 (row 1 is the heading) I have to choose some text: Text1, Text2... Depending on the choice of value (it should ...
0
votes
1answer
37 views

Single QUERY for most recents of two groups

I have two sheets with the same columns. I need a single query to pull the most recent matching record from each of the two sheets. Here I'll just show them as adjacent ranges: | Stu | Date ...
0
votes
0answers
8 views

Google Sheets URL keeps defaulting to leftmost tab

I give a colleague a link to Google Sheets, copied & pasted including the unique tab URL (#gid=123456...). However, as soon as they click, it just loads to #gid=0 (the leftmost, default tab). Why ...
0
votes
0answers
13 views

copy/paste formula with reference to another sheet?

Within Google sheets, I have a formula like: =COUNTIFS(foo!H:H,"*"&B14&"*",foo!E:E,"*"&C14&"*") which I want to copy past as: =COUNTIFS(bar!H:H,"*"&B14&"*",bar!E:E,"*"&...
0
votes
0answers
12 views

Copy a range of cells from sheet1 to sheet2, but then have the copied cells on sheet2 remain unchanged even if I erase the data from sheet1 [closed]

I am trying to copy a range of cells from sheet1 to sheet2, but then have the copied cells on sheet2 remain unchanged even if I erase the data from sheet1. The reasoning is complicated, but I'll try ...
2
votes
2answers
33 views

Is there a way to autofill cells with a formula targeting cells horizontally (in rows) while autofilling vertically (in columns)?

In the above image fields are populated as follow: A1 is empty/not important B1 = animal C1 = dog D1 = male E1 = animal F1 = cat G1 = male H1= insect I1 = ladybug J1 = unknown I am working in column ...
0
votes
1answer
23 views

In Google Sheets how can I view which cells are protected?

In my Google Sheets spreadsheet I set the Protection for some of the cells so that "Only You" can edit (only myself can edit, but the dialog text reads "only you"). Other cells do not have any ...
0
votes
1answer
15 views

Convert string to URL in column

I just want to convert IDs as strings to URLs automatically. I tried this: =HYPERLINK(CONCATENATE("http://myjira.com/projects/BUWDI/issues/";A2);A2) I got an error... What do you think?
0
votes
2answers
21 views

Google Sheets auto move to next cell

I have an ID format checker that has a row of cells that can only have one specific kind of character in each cell, and then all characters are compiled in order into another cell to create the ...
0
votes
1answer
19 views

Google Forms Quiz Response Sheet

I have added a couple of columns to the Google Sheet Responses for a quiz created using Google Forms. When creating the response sheet (created in the "Responses" tab of the quiz, it automatically ...
0
votes
0answers
14 views

Creating a hyperlink from same cell as text entry

The following code, from its original state, would let me enter a text value in column4, and the code would convert the entry into a hyperlink from what was entered. I tried to take it one step ...
0
votes
1answer
24 views

Reconnecting form responses to current sheet after file move to Team Drive

I have searched for an answer for the "current" Google Forms problem I am having. It seems the answers here for this issue are outdated as Google keeps changing their technology about as fast as I ...
0
votes
2answers
29 views

Why does only one of these formulas work in Google Sheets?

B10 = 'High" =SORT(CONCATENATE(B10,"!B3:", B10, "!AK42"), High!AL3:High!AL42, TRUE) That returns the error SORT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: ...
1
vote
1answer
21 views

How to set default Google Sheets and Google Docs account?

I have two Google accounts that I always sign into, one of them is my personal account and the other belongs to the company I work for. I created a desktop shortcut for both of these apps from chrome ...
0
votes
1answer
21 views

Sum of time in Google Sheets' chart

I tried to show a chart where the values are the sum of times. It looks ok in the spreadsheet with for example 19 hours and 25 hours but in the chart, the 19 is ok but the 25 is shown as 1. The format ...
1
vote
1answer
24 views

Using filter and importrange to get a value from Col8 when some of the rows in Col1 are subheadings

I just want to return the value in Col8 corresponding to the row that has "Residential". I can only seem to get the value that is in the top row rather than the row corresponding with "Residential". =...
0
votes
1answer
19 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
0
votes
0answers
14 views

Auto date cell when opposite cell is populated - problem with script

This question has been asked here and I'm posting a new post because I couldn't figure out how to reply to that one. I installed the script and saved it but no date appears in my sheet when I insert ...
0
votes
1answer
9 views

Create case sensitive vlookup with array based search key

I have the following case sensitive vlookup formula however search_key must be a single value here, and I need to search an array of keys. =ArrayFormula(INDEX(return_array, MATCH (TRUE, EXACT(...
0
votes
1answer
20 views

Google Sheets table of data wont create graph accurately?

So I am trying to convert some data into a scatter plot using google sheets. But 2 problems: 1) when I try and order the x and y so that the graph has x data on x and y on y it inverts it. May this ...
0
votes
1answer
5 views

Copy data validation “list of items” from one sheet to another?

On one sheet I have a column that uses "list of items" data validation which allows for a drop-down menu to extend and a multiple of options to be selected. Is there a way to automatically populate ...