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

learn more… | top users | synonyms (1)

0
votes
3answers
4k 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
407 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 | ...
4
votes
2answers
2k 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 ...
1
vote
1answer
938 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 = ...
2
votes
1answer
482 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
523 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
5answers
4k 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
1k 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 ...
2
votes
2answers
942 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 ...
1
vote
0answers
83 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
8k 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
1k 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 ...
7
votes
3answers
15k 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 ...
2
votes
1answer
2k 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.
5
votes
2answers
2k 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 | | ...
9
votes
1answer
13k 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 ...
3
votes
1answer
2k 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)) ...
3
votes
2answers
761 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: ...
0
votes
0answers
445 views

Google spreadsheets - “=ImportHtml DIV” how to?

I play Runescape and I want to make a tool that people can use to make profit in the game. My problem is that I am not sure exactly how do use a command to pull data text from a div element. The ...
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
262 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
2k 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 ...
3
votes
2answers
3k 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
3k 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
16 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
170 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
616 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
596 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
3k 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 ...
3
votes
1answer
3k 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 ...
0
votes
1answer
697 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
247 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 ...
3
votes
1answer
944 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 ...
0
votes
1answer
6k 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 ...
2
votes
2answers
4k 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(); ...
1
vote
0answers
514 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
2answers
2k 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
453 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
166 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 ...
-1
votes
1answer
1k 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
1answer
514 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
493 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 ) ...
2
votes
2answers
2k 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: ...
2
votes
2answers
1k 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 ...
3
votes
3answers
2k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
1
vote
1answer
2k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
0
votes
1answer
431 views

Get Published Google Doc in JSON

I have a published Google Doc at https://docs.google.com/spreadsheet/pub?key=0AtjON-ccqVTgdDhKeGx0UlZpUWUyLTFZV2szQlJyNnc&output=html that I want to retrieve in JSON. I've been trying this but ...
3
votes
1answer
415 views

How can we view the whole spreadsheet instead of just the form in Google forms?

I have been given a link to a Google docs form where I was supposed to enter some data. The URI looks something like this: https://docs.google.com/spreadsheet/formResponse?formkey=foo Is there ...
2
votes
1answer
4k views

Google Docs Spreadsheet - Parse Error

Not sure why this formula does not work. I just want the value to be zero if 60-D15 is negative =MAX(60-D15,0) also tried =MAX(D15,0) =MAXA(D15,0) Any clues?