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
3
votes
2answers
2k views

Single drop down with data from separate columns

I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations. ...
1
vote
2answers
141 views

Is it possible to prevent people from entering data into my Google Spreadsheet?

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2. Maybe my explanation is better with an ...
0
votes
1answer
68 views

How to restrict the number of entries for a particular column in shared spreadsheet using Google Drive

I want to create a spreadsheed using Google Drive in which some columns entries should be retricted based on given value. E.g.: I have some columns named users, item1, item2. In columns item1 & ...
1
vote
1answer
465 views

Set Selection throughout all sheets

I have a spreadsheet with multiple sheets. I want them all to scroll in sync. For example if I scroll to J18 in Sheet1, all others sheets will have the cursor at J18 and same row/column at the top ...
1
vote
2answers
773 views

How can I prevent users from reverting to an old version of a Google Spreadsheet?

I would like to allow users to edit a Google Spreadsheet, but not be able to revert them to prior versions. Is this at all possible? USE CASE The spreadsheet we use keeps track of snacks consumed, so ...
0
votes
2answers
8k views

Make first row in Google Spreadsheet column names

I cannot figure out how to turn the first row of a Google Spreadsheet into column names so that I can sort the spreadsheet by column without losing the variable names at the top of the spreadsheet. I ...
0
votes
2answers
134 views

Which formula sums all values from a column in a line that contains a specific word?

Let's use this random spreadsheet to illustrate my problem. What I want to do is a sum of all Page Views (F) that contains the Category (B) named /Online Computers. Which formula can I use to do that?...
1
vote
1answer
759 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
3
votes
2answers
5k views

Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
3
votes
1answer
2k views

Google Spreadsheet SQL subquery “where not in”

I'm attempting to perform the following SQL command concept, but with two sheets in the same google spreadsheet document: select * from table1 where column1 not in (select column1 from table2) How ...
-1
votes
1answer
121 views

How can I get a column with the sum of the proceeding rows from another column?

I've got a series of withdrawals and deposits for an account, each in a row. I'd like another column that for each row, displays the total amount in the account after that row's withdrawal or deposit. ...
1
vote
1answer
2k views

Stop lines in GDocs chart overlapping/obscuring other lines

How can I stop lines in GDocs chart overlapping/obscuring other lines? See for example the green line in the graph below, which starts up high, butthen goes behind other lines such as the yellow one.
-1
votes
1answer
69 views

How to have charts include added rows in google docs

The problem is, it it's modifying my range selection. In the chart I select ranges Sheet2!A:A, Sheet2!B:B, but after clicking ok, and going back into the chart, it modified my range selection to ...
3
votes
1answer
885 views

What is the fastest way to use Google Spreadsheets by several users - from the same account or from different accounts?

Terminology - I use spreadsheet for a document and sheet for a sheet within the document. TL; DR If several users must work on data, stemming from the same source, which is fastest: one Google ...
2
votes
2answers
78 views

Formula required for date and counting fabrics

How do create a formula that counts the days I am waiting for fabric to arrive, however when it does arrive I need it to stop counting the days. =TODAY()-TO_DATE(A41) This works great, however it ...
5
votes
1answer
29k views

How can I highlight selected text in a cell and not the whole cell? [duplicate]

In my Google Spreadsheet, I have a lot of text in a cell and want to highlight a small portion of this text with a particular colour. But, whenever I try to do this, the formatting gets applied to ...
4
votes
1answer
2k views

Google Sheets referenced cells and sorting

I am working on a grades input spreadsheet. Consider 3 sheets: A list of assessments containing references, names, weighing etc. A list of students, with IDs etc. A table with student names in rows ...
0
votes
2answers
2k views

Add two variables to a Hyperlink

I know this works: = hyperlink( "https://www.google.com/search?q=" & B2 ) But I can't get two variables to work so the link would look like: https://www.google.com?var1=[insert cell contents]&var2=[...
3
votes
2answers
1k views

How to summarize many-to-one table in Google Sheets

I've got two separate sheets we'll call 'Sheet A' and 'Sheet B' with a table on each. The table on 'Sheet A' has a Many-to-one relationship to the data on the second sheet. On the second sheet, I'd ...
3
votes
1answer
1k views

How to select a particular cell from an 'array formula' result?

Take this formula: =GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1)) This formula returns an 'array result', meaning it generates a little table over multiple cells, like this: The top-left cell ...
7
votes
2answers
8k views

How can I dynamically format the diagonal cells in Google Spreadsheet?

I want all of the diagonal cells (A1, B2, C3, D4, E5, etc) of a Sheet to be formatted as Black Background, White Foreground. Is there any way to do this for a Sheet that constantly increases in size? ...
4
votes
1answer
6k views

How do I refer to a range in another sheet?

Sheet 1 has numeric values in the B column. I'd like to sum them into a single value within Sheet 2, preferably without having to copy them individually first. According to the docs, I can copy a ...
2
votes
1answer
200 views

Formula value change after a script pastes into another range

I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with this?...
2
votes
1answer
2k views

How to make my Google Spreadsheet automatically calculate “days since”

I have a spreadsheet with several columns and one of them is for date (column D). How do I make the next column (E) automatically display the days passed from date in D until now on open? I have ...
5
votes
1answer
24k views

How can I use Google Spreadsheet to format Date in DD/MM/YYYY format?

First thing's first: my spreadsheet's locale is properly set to UK: If I paste in the following date: 30/09/2013 21:15 Then click Format > Number > Date time Nothing changes. I also can't seem ...
2
votes
1answer
5k views

Changing the A B C … column labels

The picture above shows what I am referring to. I'm looking for a way to change these " A, B, C, ..." labels to describe better the content below each. A would contain what date, B what length I ...
3
votes
1answer
891 views

Leads per Week For Last 10 Weeks Formula With Countif

I have a long list of dates (like below) that indicate Leads and I am trying to get number of leads per week for the last 10 weeks. I know I can get the current week number with MID(TEXT(Today(),"yyww"...
1
vote
1answer
262 views

How can I copy the lower triangle into the upper triangle?

I have a matrix in Google Drive Spreadsheets. Only the lower triangle is filled. I would like to copy the values of the lower triangle to the upper triangle. How can I achieve that? Example: ...
1
vote
1answer
193 views

How do I copy rows with certain criteria from multiple spreadsheets into one spreadsheet

I made a Google Form for assigning a task to myself or a colleague. For Example: spreadsheet name: Science, Column 1=Name 2=task 3=due date spreadsheet name: Mathematics, Column 1=Name 2=task 3=...
0
votes
1answer
2k views

Where are the conditional formatting custom formulas?

I would like to use custom formulas in the conditional formatting panel on Google Spreadsheets, such as indicated in that link: https://support.google.com/drive/answer/78413#formulas The problem is ...
0
votes
2answers
277 views

Run formula on different sheet

I need help applying this formula to a different sheet: =SUMIF(A2:A6,"3",H2:H6) I'm basically trying to discover expenses for the month of March, and hopefully having a year constraint in there too. ...
3
votes
1answer
503 views

Referencing cells above and to the left in script

I'm trying to make a script for a custom function in google spreadsheet to work out the hours worked in a day function hwtd(). I've got it perfect in the cell (((F8-E8)+(F9-E9))+(IF((F10<E10), (F10-...
5
votes
2answers
7k views

How do I format a cell to be in alternative currency without decimals in Google Spreadsheets

I am really scratching my head, but it seems like this is not possible in Google Spreadsheets. I want to format a number cell in kroner without any decimals. All I get is "kr 38,434.00" I want this ...
1
vote
1answer
11k views

Take SUM of colored cells in a column in Google Spreadsheets?

I have a column of numbers, where I've changed the background color of some of the cells to blue. All of the other cells in the column have the regular white background. Is it possible to create a ...
2
votes
1answer
2k views

Calculate monthly sum() on pivote table

I have a Google Form. Upon submit following fields are populated: Timestamp Product Description Unit Price Quanitity I have generated a pivot table from this data, in a Google Spreadsheet. The ...
3
votes
2answers
619 views

Combine text from rows got by specified date in a Google Spreadsheet

I am trying to create a summary sheet that combines the values and descriptions from my bank transaction into individual dates I want to paste the transaction csv into google spreadsheets. The ...
1
vote
0answers
337 views

How to make script slow down in GoogleSheets Script Editor?

I have a script thats attached below, and the problem I'm facing is that after it has keyed in "ticker" to Raw Data in b1, my entire sheet is never done loading and thus when its time to check if it's ...
2
votes
1answer
120 views

How to capture a cell when entered or selected?

I am trying to capture, programmatically, the on cell entered and on cell selected method, but I don't seem to be able to find any documentation on it. I can get the time stamp for exiting the cell ...
1
vote
1answer
879 views

Fix Value of Row or Column from Another Sheet

In a given sheet, if you have a formula that involves a value, it's possible to fix either the column or row for that value as you expand the equation, i.e. : =A1 + A!2 But how do you do this when ...
2
votes
1answer
2k views

Formatting integer to Gold/Silver/Copper using custom number format

I have number in coppers: there are 100 coppers to a silver and 100 silvers to a gold I need the raw number to stay in the cell so it can be used in calculations. Ideal format: 1 ==> 1c 123 => ...
2
votes
2answers
492 views

Incrementing value read from spreadsheet exploding. 1 + 1 = 200 000 000 000

I read a value (in this case 7) from a Google Spreadsheet with a script, increase that value using source++ and write the increment back to the spreadsheet. I expect a series of 7, 8, 9, 10. Instead,...
2
votes
2answers
332 views

Automating Calls to New Google Sheets Add-ons

I'm building a Google Spreadsheet that uses an official add-on to pull data from Google Analytics using the new add-on store. What I'd like to have it do is automatically refresh the data every day at ...
1
vote
2answers
48 views

Find items that are not duplicates

I have these cells A1 through A7: Mary Susie Jane Mary Susie Elizabeth Mary I want to get the names that do not appear multiple times, so my results should be Jane and Elizabeth. I don not want ...
2
votes
2answers
41k views

Check if Column contains value, when found return contents of another column in that row

I'm using a Google Drive spreadsheet to organize guests I want to invite to my wedding. On one sheet I want all my guests and information relating to them (are they friend/family? Are they an usher/...
3
votes
1answer
6k views

IF Statements with 3 options

I have 3 possible evaluation values in my spreadsheet (I have to use the values with digits and words): 1.0 - Doesn't meet expectations 2.0 - Meets expectations 3.0 - Exceeds expectations The goal - ...
6
votes
1answer
13k views

How do you conditionally join text in Google Sheets?

So, I thought this would be relatively straightforward for an advanced user, but this one is troubling my brain. I have a schedule of people: 1 2 3 4 5 6 7 8 John D. a a ...
9
votes
1answer
9k views

Cross-reference sheets on Google Spreadsheets

I can just do B12 to reference a field on the same sheet I'm working on, but how do I reference B12 on another sheet?
2
votes
1answer
254 views

Geocode in Google spreadsheets, keep results

I have a form for internships and I am passing over the address of the internship provider to a function which gives me latitude and longitude for it, which is then consumed by a webmap using tabletop ...
5
votes
1answer
3k views

Auto-sort column Google Spreadsheet on incoming Google Form response

How can I get the Google Spreadsheet to automatically order the incoming form responses alphabetically (2nd column)? I know how to do this manually or by going in and editing a cell, but cannot get ...
4
votes
1answer
8k views

Multiple Conditions on a CountIF?

I have a spreadsheet that is essentially a todo list for different groups. I have a column for Group and Status. Both have data validation drop downs so I can reliably match the text content. I have ...

1
131 132
133
134 135
137