One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
1answer
69 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 ...
1
vote
3answers
2k 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" ) - ...
1
vote
0answers
51 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
493 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 ...
5
votes
1answer
4k 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 ...
1
vote
0answers
185 views

Use of importdata with arrayformula

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

Mail merge from Google spreadsheet to Google doc

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 ...
1
vote
1answer
3k views

Google Docs: 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 ...
2
votes
1answer
100 views

Google Spreadsheets Validation list with Label

Thanks in advance. I have just started using Google Spreadsheets so am very new to it's ways! So far it looks incredibly powerful! I am using some data in a sheet called Jobs as a reference to ...
3
votes
2answers
315 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
695 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
2k 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
980 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 ...
3
votes
1answer
3k 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 ...
0
votes
1answer
171 views

Help with a conditional formatting script in Google Spreadsheets

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
495 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
444 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
149 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: ...
1
vote
2answers
2k views

Google Spreadsheet Change X and Y Axis

I am trying to graph the temperature of a substance versus the time the substance has been heated. My data is as follows: A | B Time (Minutes) | Temperature (ºC) 0 ...
1
vote
1answer
293 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 http://goo.gl/1TcQrW (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 ...
1
vote
1answer
497 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 ...
1
vote
0answers
230 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
728 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.
0
votes
2answers
437 views

How do I link two Google Doc spreadsheets so that entering a numerical value in one cell of Sheet 1 produces an 'x' in the same cell of Sheet 2?

I know about the importRange function to link two sheets, but I wonder if there's a way so that when a cell's contents are imported to the second sheet, the second sheet will autopopulate the ...
4
votes
1answer
365 views

Monitor webpage changes using Google Spreadsheets

I want to monitor a web page. The best solution I found was Google Spreadsheets (apart from feed43 which has limitations). I'm using the importHTML function in Google Spreadsheet which will update ...
2
votes
1answer
800 views

Drag Autofill of a Multi-Column Pattern in Google Spreadsheet

I'm trying to drag copy some cells, which are on top row for headers, they get their values from a VLOOKUP formula (Basicaly all group names are in column B and I want them as column headers). Before ...
2
votes
1answer
213 views

Grading a Google Spreadsheet by answers given in a Google Form

I'm currently working in a school and students answered a 50 question form on ICT Ability. The results of this form have been put into a spreadsheet and I have to order the names in terms of ability. ...
3
votes
3answers
2k views

Google Spreadsheets date and time format

I have lots of dates in the first column in the format August 23, 2010 at 11:01PM I was wondering if anyone knows a way to get them formatted in a way that I can then use for date and time ...
1
vote
1answer
2k views

Google Spreadsheet how to use an if statement over several cells

Is there a function that allows me to calculate the total value of column B if column A is equal to "some text". I'm making a budget for myself. Currently, my Google Spreadsheet looks something like ...
1
vote
1answer
2k views

Auto populate data in Google Spreadsheet from data in separate spreadsheet?

So, I'm in the situation where I have a Google Spreadsheet with people's names, phone numbers, addresses, etc. Is it possible to, from another spreadsheet (not sheet, different workbook), search the ...
3
votes
1answer
173 views

Get a list of items missing from array in google spreadsheet

I have a spreadsheet where I'm using the trailing set of columns as a kind of foreign key; I'm trying to throw an error if someone enters a value that doesn't exist. I'm thinking one way to do this ...
2
votes
3answers
2k views

Split, reorder, concat inline in Google Spreadsheets

I want to split a cell, reorder the pieces and recombine. How do I do that inline. For example: | name | reordered name | | Page,Larry | Larry Page | | Brin,Sergey | Sergey Brin | ...
1
vote
1answer
77 views

Publish last 5 entries from a Google Form

We currently create and html table to list our last 5 Webinars. This is done manually. We are adding external contractors into the mix and would like to give them the ability to enter basic webinar ...
3
votes
1answer
3k views

Panel with Vertical Scroll Bar that allows navigating to different worksheets

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following ...
1
vote
1answer
437 views

How can I speed up my Google Spreadsheet Script?

I am somewhat new to JavaScript and very new to using it in Google Spreadsheets. I have read that fewer calls to Google Spreadsheet API speeds things up, but I feel like I have as few as possible at ...
3
votes
1answer
320 views

How can I use “fill series” without appearance of unwanted commas?

before: V 2013 I am using fill series to fill cells below. expected: V 2013 V 2013 V 2013 result: V 2013 V 2,013 V 2,013 I found only ...
1
vote
1answer
56 views

A simple spreadsheet sorting

In a Google Spreadsheet I have different groups of cells in one column to auto-sort. All cells in this column have same pattern of data, all datum have it's own cell and it's needed to be sorted into ...
4
votes
1answer
83 views

Notes In Published Google Spreadsheets

I've developed a website for our active adult association. I upload an Excel spreadsheet to Google Drive. It has notes attached to cells. The notes display fine in the converted version in Drive. ...
0
votes
1answer
78 views

How do I reference a second sheet column across a row of numbers in sheet 1

I'm trying to look-up a value in sheet 2 from a number on a row in sheet one: it looks like this: Sheet 1: Row4 Number of units 0 5 25 10 15 And sheet 2: column a column b 1 2 3 ...
1
vote
0answers
62 views

Automating Google spreadsheet to transfer new info

I'm looking to setup a production line with Google Spreadsheets. Here's how I intend it to work: Our Sales Rep John has a spreadsheet specific to him only. He takes in sales leads, and inputs the ...
1
vote
1answer
724 views

Protect Google Spreadsheet formatting while allowing changes to data

I would like to protect the formatting of a cell (or a range of cells) in a Google Spreadsheet, but still allow changing the data in that (or those) cell(s). For example, I want to add a validation ...
6
votes
2answers
4k views

Colorize a cell in Google Spreadsheets based on cell data

I'd like to colorize a cell based on it's contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ...
0
votes
1answer
502 views

How to GROUP and ORDER data in a Google Spreadsheets?

I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions. Can I order by sum? This is an example file I created: order by sum
1
vote
1answer
1k views

Data Ranges in Custom Google Spreadsheets Function

I'm trying to create a custom Function in Google Spreadsheets that takes a range as input parameter. function Example(cells){ var values = ...
2
votes
2answers
681 views

Date Formatting in Google Spreadsheet

I use a spreadsheet in my medical office for referrals. It includes places to fill in these three areas: date faxed, date of the appointment, date the appointment office notes were received. The ...
0
votes
1answer
2k views

Auto date added to adjacent cells when data is entered

I am trying to create a spreadsheet to track student loan outs that will automatically put the date in an adjacent cell. The spreadsheet would be set up like this: Column 1 would be "name" Column 2 ...
0
votes
0answers
27 views

How to have function input row/cell automatically update in response to new data? [duplicate]

I apply a function, such as SUM(A10:A20) to a set of data. When I give A21 a value I would like SUM(A10:A20) to automatically update to SUM(A11:A21), "following" the last X number of cells in a ...
2
votes
2answers
2k views

How to extrapolate data in Google Spreadsheets?

Can I extrapolate data in Google spreadsheets? Given a set of values as 10 12 14 16 18 I would like to "estimate" that the next values will be 20 22 24 and so on. I realize there are several ...
1
vote
1answer
335 views

Duplicate check while creating Google Contact with Google Apps Script

I have the following code which creates Google Contacts from a Spreadsheet. The problem is whenever I run the code it creates contacts but has no functionality to check if the same Google Contact ...
2
votes
2answers
337 views

How to get a value from column 'A' in every 30th row in Google Spreadsheet?

I have a sheet from which I want to get values from column A of every 30th row. I tried =A(2+30) but it just gives me an error.