A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

0
votes
1answer
651 views

How to read a multiple values in cell in each row (separated by comma) in a Google Spreadsheet

I have problem in reading cell with multiple values and comparing the each value with column from a-z if match found then set value of the cell has '1'. Problem: I need to take multiple company names ...
1
vote
1answer
118 views

Adding a few lines of information to a grid

I have made out an 8 column, 21 lines, informative health chart with grid lines. I would like to add an afterthought running from left to right UNDER the grid lines that will PRINT out with the ...
4
votes
2answers
5k views

Pull CSV data from URL to Google Spreadsheet

I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet ...
3
votes
4answers
5k views

How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
5
votes
2answers
7k views

Google Spreadsheets Filter() - referencing source array in criteria

The filter() function looks through a source array, and filters it according to condition =FILTER(A:A,ISNUMBER(A:A)) My source array is a rather long formula, so I end up repeating the same formula ...
2
votes
3answers
3k views

how do I get named ranges in Google Spreadsheets to be more reliable/current?

I'd like to pull values from a named range into a calculation. I have this function: function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } Seems pretty simple. I ...
2
votes
3answers
15k views

How to auto increment a field in Google Spreadsheet

In my spreadsheet I have a column called S+ No. How to auto increment this for each row, like: S1,S2,S3,S4 In short how to make it dynamic.
1
vote
2answers
985 views

Categorize cells in Google Spreadsheets

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this: | Who | What | Category | When | Amount | | Gui13 | Vegetables | Meals | ...
5
votes
3answers
6k views

Google Spreadsheets formulas: compare each cell of two rows

I am working on a Google Doc spreadsheet using a not-so-simple formula. The formula works but is not as I want it. What I want to achieve is: "Compare each cell from T3 to AM3 to each cell from T2 ...
3
votes
1answer
5k views

How can I apply a script to only one specific sheet within a spreadsheet

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A function onEdit(event){ var sheet = ...
3
votes
1answer
2k views

How to get the average of the last four values of a row

I have a spreadsheet that looks like this. It basically tracks the ratings of each show. As you can see each row as a varying number of values. What I want to be able to do is to get the avg for ...
8
votes
1answer
1k views

How do I decimal-align numbers in Google Sheets

In Google Sheets, I want to decimal-align numbers without adding trailing zeros in the fractional part. Instead of: I want: +------+ +------+ | 56.0| | 56 | | 27.0| | 27 | | 83.0| ...
5
votes
7answers
13k views

Is there a way to link a “Choose from a list” style question in a Google Form to a list of data (e.g. in a spreadsheet)?

Would really appreciate some help from any experts in Google Forms. Is there a way to link a "Choose from a list" style question in a form to a list of data (e.g. in a spreadsheet)? This would mean ...
2
votes
3answers
3k views

how do I add isblank(ref) criteria to arrayformula?

EDIT: Self-Answered This is an improvement of work from this post. I have an array formula that is similar to this image. It sums column A when the date in F matches the due date (C). What I want ...
2
votes
2answers
1k views

Count Columns by Condition in Google Drive Spreadsheet

I have a column (A) that contains a series of dates in the format mm/dd/yyyy. I want to run a count to return how many of them were in 2013. Is this possible? I tried using this formula, but it didn'...
2
votes
1answer
149 views

In a Google Sheets chart how do I prevent “Other” in the legend?

When there are over 8 series in a chart (this is true at least for a pie chart) - Sheets combines all other series above the 8th into "Other" this is really counter productive when there are only 9 ...
2
votes
3answers
11k views

COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough ...
1
vote
1answer
2k views

How to split, trim, and sum up numbers in spreadsheet text cells?

I have a series of cells in a Google Spreadsheet containing string such as: A1 = "X5.1 Y4.3 Z2.8" B1 = "X2.5 Y1.9 Z4.8" C1 = "X6.2 Y1.9 Z3.4" I need to get the sum of all the numbers that appear ...
3
votes
1answer
2k views

How to view or edit Google Spreadsheet offline?

I have followed the instructions to setup Offline mode access to Google Drive and it worked well. Surprisingly Google Spreadsheets do not open on offline mode, giving an "internal error", however can ...
13
votes
3answers
35k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
4
votes
2answers
6k views

Hot keys to switch between tabs?

Does anyone know if there´s a shortcut to switch between tabs in Google Spreadsheets? If you use CTRL+PgUp / CTRL+PgDown like in Excel you switch between Chrome or Firefox's tabs instead.
7
votes
3answers
7k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 players. I have the following: | Players | |----------| | Dave | | Paul | | John | | ...
20
votes
1answer
32k views

IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if($C7 == "U.S.A." || $C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...
8
votes
1answer
8k views

Min/Max of a row only if value in other row is X

I'm a bit vague as to what I am looking for/asking about, but here is an example: A | B 22 | 5 15 | 5 2 | 10 19 | 10 5 | 10 =min(A2:A6, if(B2:B6=5, 5)) ==> 15 =min(A2:A6, if(B2:B6=5, 10)) ==&...
4
votes
2answers
3k views

Protect Formatting (Validation)

I have a very complicated tool that I developed that produces a mass amount of information. Technically, the whole sheet should be a database drive website. However, it is a spreadsheet: ...
2
votes
1answer
1k 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 ...
2
votes
1answer
294 views

google spreadsheet question about data linking between sheets

Here is my spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AgmD6-Z4d2aVdE9pR095OTJrVE1wS3FIZzZpVFRJMmc#gid=4 What I want to happen is I enter data in the sheets marked Head, Neck, Shoulders ...
2
votes
1answer
3k views

Google spreadsheet sync with gCalendar

I create a GTD system based on excel, whose link I report here. The advantage of having this gtd is that I can share it with my european colleagues without the need of having a dedicated software ...
5
votes
2answers
5k views

Split content of one row into multiple rows?

I have a table where each row contains data like this: Team name | First member | Second member | Third member | Team description Would it be possible to transform/break this data so that each row ...
1
vote
1answer
5k views

How can I import Google Calendar events into a Google Spreadsheet?

I just need rows with name, date/time, and location. Ideally it should be done automatically every n hours or at every event added. I found a script solution on Stack Overflow, but is there any ...
0
votes
0answers
19 views

Google Spreadsheet: reference seperate spreadsheet [duplicate]

Possible Duplicate: How do I link a cell in Google Spreadsheets to a cell in another document? Is it possible with a Google Spreadsheet to reference a cell to another spreadsheet using a ...
5
votes
1answer
217 views

Auto Sort Sheet on second entry

I loved the script put forward by w0lf mostly it did the trick however I cannot figure out a way to have it sort only when I enter data in the second column of 4 columns it does it when I enter data ...
2
votes
2answers
1k views

Is there a filter delimiter in Google Spreadsheets?

I'm trying to build a list of servers in a Google Spreadsheet. Each server has multiple services, and I would like to be able to select a single service and see all the servers providing that service. ...
3
votes
1answer
1k views

Add headers to Google Spreadsheet chart

I have a chart in a spreadsheet. If I click on it and select 'advanced edit' > Start, it offers to 'Use row 12 as headers'. How can I use a different row for the header names? My data has rows for the ...
3
votes
2answers
7k views

More Efficient Filtering Script

I'm exactly one day into writing Google scripts. I've managed to write one that will filter data in a spreadsheet with 24 columns and, for now, about 900 rows. The problem is, it's slow. Any ...
5
votes
1answer
6k views

filter or sort Google Spreadsheet based on condition

I have an 8 column spreadsheet. I want to sort/filter based upon the value of the 4th column. In a separate sheet within the same spreadsheet, I want to print a list of each row matching a speific ...
1
vote
1answer
1k views

SPLIT function in Google Spreadsheets with OR statement

I want to use the split function, but with different delimiters. I thought of something like this: =SPLIT(A:A;OR("-","&")) but it doesn't work. Is it possible to have Boolean statements within a ...
1
vote
1answer
343 views

Google Docs time stamp?

The following code was almost exactly what I was looking for to timestamp the adjacent cell after being edited. However, I need to incorporate the currnet date as well as time. The code below gives a ...
7
votes
1answer
2k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
2
votes
1answer
9k views

How do I use the INDEX or OFFSET functions in Google Spreadsheets?

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month). The last 24 columns are static and the values are ...
3
votes
3answers
17k views

Google Scripts : How to distinguish Empty cells from Zero

I want to count a number of specific responses from a form where the response can be left empty, zero or any other number 'd'. The following code: var data = sheet.getRange("B2:L").getValues(); var ...
1
vote
0answers
852 views

Google Docs capabilities

We've created a commission calculator to help our resellers work out what commissions they will receive on a deal. We built it in Excel 2010 but want to move it to a web based platform that will ...
0
votes
3answers
5k views

How does one insert a new line in a cell in a Google doc spreadsheet?

I saw a question with this earlier but nobody seems to have it answered in a way that helps me. I need to paste text into a Google Docs spreadsheet cell but it needs to have two new lines in it. ...
2
votes
1answer
1k views

Sorting a protected range in Google spreadsheets

I've been trying to make the spreadsheet automatically sort some data, and after some searching I found this post: Automatic sorting. It works just perfect with my permissions as the owner, but when ...
0
votes
1answer
189 views

Automatically add values before and after Google spreadsheet

I have a library of flags online and would like to have a client-foolproof solution for adding flags. Right now my function looks like this: =image("http://www.website.com/flags/us.png", 3) But I ...
0
votes
1answer
3k views

Set the headers in a Google Drive spreadsheet to move on screen as I scroll down

How do I set the headers in a Google Drive spreadsheet to move on screen as I scroll down the spreadsheet?
0
votes
2answers
2k views

Query multiple Google spreadsheets

I have the following sheets: ListA ListB ListC Note: at each sheet I have a column which contains YES or NO. How do I query all my lists (spreadsheets) where myBooleanColumn equals to YES/NO?
1
vote
0answers
675 views

How to make a query with Google Spreadsheet, that returns rows if deadline is two weeks from today, one month from today, 2 months from today, etc

I have a spreadsheet that contains several columns with project information. I basically want to modify the following formula: =QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 ) ...
3
votes
2answers
5k views

Google Spreadsheets: Counting minutes/seconds

I am using format-value-time to have cells where I input minutes/seconds, like 13:45 for 13 minutes & 45 seconds. If I have several of these cells, how can I add them all up? Example: Cell1: 00....
3
votes
3answers
5k views

Auto date insert when opposite cell is populated

I am creating a spreadsheet which will contain records of my daily expenditure. Something like the following: A | B ---------- 0 | 50.04 0 | 20.00 0 | 19.99 0 | 2.40 What I am trying to ...