The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
3
votes
1answer
549 views

Repeating functions inside “if”

I was creating a spreadsheet the other day and I stumbled across the following "problem". I wanted to test the result of a function against a value and if that was true, display the result of the ...
2
votes
1answer
251 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
2k 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 ...
1
vote
1answer
78 views

Finding the most important contributor to a column in Google Spreadsheet

I have a column in a google spreadsheet that's calculated as a function of a few other variables (columns). Is there a function that I can use to find which of the variables has the most influence ...
0
votes
1answer
87 views

Prevent the cell from expanding to the size of cell contents in a Google Spreadsheet every time I open it?

I know how to "Wrap text" to make this happen, but my issue is I am having to do this EVERY time I open a spreadsheet in Google Docs. It is a quick couple of clicks, but it adds up. Is there a way to ...
3
votes
1answer
4k views

Google Spreadsheets Data Validation - Unique Values

I have a google sheets document which contains two sets of data in separate worksheets: Public IP addresses Private IP addresses In my private IPs worksheet I want to create a dropdown which lists ...
5
votes
1answer
2k views

Is it possible to insert text from a Google spreadsheet into a text box in a Google document?

I need to insert text into a template that contains multiple text boxes in a variety of orientations. I can do that by inserting a text box from Google Drawings into a Google Doc and formatting it to ...
9
votes
1answer
17k views

IF function in Google Spreadsheets displays Parse error, what is wrong?

What is wrong with this code? = IF (F3+E3+D3 > 0, F3+E3+D3, 0) I want the cell to contain the sum only if it is positive. If it is negative I want zero to be there. Google Spreadsheet gives me ...
7
votes
2answers
11k views

Google Sheets says “spreadsheet too large” when it isn’t

I have an XLSX spreadsheet. It has 68251 rows and 94637 cells, and it's on-disk size is 1510842 bytes. When I try to upload it, Google claims that it exceeds the 400,000 cell limit. The cell count ...
4
votes
3answers
22k views

Create a function for an entire column in Google Spreadsheets

I'd like to set a function for an entire column in Google Spreadsheets, and I'd like for that function to get applied whenever I add new rows to the spreadsheet. I know that I can set a function for ...
3
votes
3answers
14k views

Making all cells in a column links from the values within the cell?

I have a column of cells in a Google Spreadsheet with values such as: 512 2123 2342 3532 What I would like to do is convert all these into a hyperlink and keep the value as the link text: http://...
0
votes
3answers
6k views

Counting unique values based on multiple columns

I am working in Google Spreadsheets and trying to do some counting that takes into consideration cell values across multiple cells in each row. Here's my table: |AUTHOR| |ARTICLE| |...
1
vote
2answers
2k views

Send email notification based on timeframe and a given email address from a cell?

How specifically does the code found in this article need to be modified to allow for a specified email address column to inform who the reminder should go to? I have an example spreadsheet here with ...
2
votes
1answer
4k views

How to add multiple spaces between characters in Google Spreadsheets?

If I want 10 spaces between characters "a" and "b" I press the space bar 10 times, but when I hit "Enter the cell defaults to only displaying 2 spaces. When I click back to edit that cell the display ...
10
votes
3answers
10k views

Pass range in Google Apps script custom function without using A1 notation

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to ...
1
vote
3answers
2k views

Google forms script to delete responses counter

I have tried and combined a few pieces of script to delete rows, but this does not reset the counter. Help resetting responses using script would be appreciated. My copy sheet function, and delete ...
3
votes
1answer
213 views

Can I insert an hyperlink into a non empty cell of a Google Spreadsheets documents?

my problem is that if I add something like this in an empty cell of a Google Spreadsheets documents it work great =HYPERLINK("www.google.co.uk"; "Google") But If I add this link to a non empty cell ...
14
votes
3answers
30k views

Cell reference with colour formatting

Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference cell's value. But if ...
1
vote
1answer
1k views

Google spreadsheets—sum cells from another row based on a cell value

My current code is: (SUM(INDIRECT(ADDRESS(ROW()-(I4:I- 1);COLUMN()-4;4)&":"&ADDRESS(ROW();COLUMN()-4;4)))*( ( J4:J ) / 10 )))) I want to sum cells from Gx – Gy but range is determed by cell ...
1
vote
1answer
857 views

Run a custom script more than 20 times in Google Spreadsheet

I need a way of running a custom script more than 20 times in my spreadsheet. I'm creating a class assessment spreadsheet that will be used as a master across my school, and it needs to retrieve the ...
7
votes
3answers
18k views

Duplicate Google Sheets' protected cells [duplicate]

I have created my spreadsheet with protected cells scattered throughout the document in Google Sheets. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep ...
3
votes
1answer
3k views

Sum a column except current cell

Is it possible to sum all values in a column EXCEPT the value in the current cell? That is, the cell that is to hold the result. With a normal SUM(E:E) command the result is #REF! with a Circular ...
1
vote
1answer
346 views

Find the maximum occurrence across sheets

This is kinda difficult to explain... Is it possible to let Google Sheets tell me what the maximum value is from some chosen columns in MORE THAN ONE sheet (in the same document)? I namely need to ...
1
vote
2answers
4k views

Dependent validation in Google Spreadsheets

Here's what I'd like to do. The first selection is Subject (as in a school course). There are 5 subjects. Then there are three sub-selections in drop down lists for each subject: Subject Test Type ...
1
vote
2answers
3k views

Making a header row in a Google spreadsheet

Is there a way to put text in a row at the top of a spreadsheet, like a header? So it is separate and fixed from the columns below it? In other words, I want a header row at the top and the columns ...
0
votes
1answer
67 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 & ...
4
votes
1answer
3k views

Google Spreadsheets: IF not working

I have problems with my IF command in Google Sheets. If gives #ERROR! all the time. See this simple test: Not even if I put in the number directly and avoid references: Am I using the IF wrong? Or ...
1
vote
1answer
352 views

Using a dynamic OFFSET & ISBLANK() function

I have a spreadsheet I'm working on that we use to run large corporate events. Each segment length has a length as well as corresponding start time. I want each cell in the time column to look above ...
2
votes
3answers
3k views

IF statement in Google spreadsheet

I am trying to sum up the total of all values in a coloumn that are POSITIVE. Also, I have some subtotals in the coloumn that should be left out from this sum. =SUMIF(E6:E65 ; ">0" ) - SUM(FILTER(...
0
votes
1answer
85 views

Substitute for Lookup() in Google Spreadsheets

I am making a bookkeeping sheet. The aim is to create rules, that search imported fields from our bank statement, and if a match is found automatically fill the adjoining cells with the rules data. ...
2
votes
1answer
1k views

Grouping columns in Google spreadsheets (and summarize them)

Lets say I have a table like this: A | B | C 1 | 4 | 3 Is it possible to group these columns together so that I can expand and collapse them with a specific formula. For example with SUM I would ...
8
votes
7answers
59k views

Adding a header and footer to a Google Spreadsheet

I'm in the process of changing to Google Drive from MS Office for all aspects of my work. Pretty much everything I need is there, but the one thing we can't work out how to do is how to get a header ...
3
votes
1answer
2k views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on individual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
1
vote
1answer
5k views

Mail merge from Google Sheets to a Google Docs [duplicate]

I want to print out tests for a class I teach, but I want each to be identical except for the name of the student at the top. I want this to be drawn from a Google spreadsheet that I've created that ...
2
votes
1answer
12k views

How to paste a cell range automatically - Values only?

I need some "live" cell data (values only), which get their data from another sheet; an arrayformula is not sufficient. I'm thinking something that copies column A:13-down from Sheet1 (whenever it ...
3
votes
1answer
504 views

Validation list with label

I am using data in sheet Jobs as a reference to validate a field in Sheet 1. The data in Jobs is: Job Name Job Number -------------------------------------------------------- Site ...
3
votes
2answers
395 views

Google spreadsheet: Cannot select pre-selected date in Date picker

In a Google spreadsheet, when having a cell formatted as date, or with a data validation set on date, a date picker appears when double clicking this cell. If the cell is blank, the picker's date is ...
1
vote
2answers
2k views

Show data from one sheet on another if date is today

I have a list of people responsible for data verification that are changing every day. The schedule is on one sheet. And data verification is on another. What I need is to show only responsible ...
1
vote
2answers
3k views

Can I copy row from one Google Spreadsheet to another based on certain criteria

I have a spreadsheet to track student loan outs: Column A is the student name Column B is how many loan outs they have received Column C is the class period Column D is the teacher name ...
2
votes
1answer
2k views

formulas to transfer certain data from one sheet to another

I have a list of volunteers in sheet one. They have selected what programs they want to participate in with a yes or no in the column under that program. How can I create a formula that says if this ...
5
votes
1answer
15k views

Count occurrence of a word in Google Spreadsheet

I've got a ton of cells (say 6x20) that have various names in them. I'd like to total the number of times a name in another field matches any of the other cells. Alice Bob Claire Doug Alice ...
-5
votes
1answer
267 views

Help with conditional mean in Google Spreadsheets [closed]

I'm having the following problem: If text values are similar in Column B then gather $$ values found in Column M and display the mean of those fields in Column N.
1
vote
1answer
1k views

SUM of all cells in another column, except above rows

Each cell in column A should report SUM of every cell of column B, except rows above those cells. Sample: | A | B | --------- | 4 | 1 | | 3 | 0 | | 3 | 1 | | 2 | 1 | | 1 | 1 | | 0 | 0 | How can I ...
1
vote
3answers
2k views

Sum of two numbers with a minimum of zero

I have a Google Spreadsheet with two columns of numbers (A & B). I want to add these numbers together and put the result into a third column (Results). If the sum of these two numbers is less ...
1
vote
1answer
284 views

ImportHTML and SUM the column

I am looking to use the importHTML function for loading a page and I would like to sum a particular column in Google Spreadsheets. But I am not having much success. This is the URL: =RIGHT(index(...
2
votes
2answers
11k views

Change X and Y Axes

I am trying to graph the temperature of a substance versus the time the substance has been heated. My data is: A | B Time (Minutes) | Temperature (ºC) 0 | 22.8 .5 ...
1
vote
1answer
412 views

Google Docs: How to find and display matching value on new sheet based on two criteria

I have created a demo spreadsheet to help explain. (This is a small set of example data, the real set has more values [and the data may or may not be there from week to week]) I will have a static ...
16
votes
1answer
10k views

Add all positive values in column

In MS Excel I can add all positive values in a column with SUMIF(A:A,">0") But this isn't working in the Google Docs Spreadsheets. The ">0" part is not parsed and gives an error. Is there ...
2
votes
1answer
2k views

Password protect a column in Google Spreadsheet

I have encountered a problem that I need a hand with. I have already done numerous searches of this forum and others to see if I could solve it that way but it appears I have come a cropper. So ...
2
votes
1answer
1k views

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet?

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet? Some of the responses on one will be public and viewable by anyone, but others will be private.