One of productivity apps in Google Drive, that allows you to create and edit worksheets
1
vote
1answer
152 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: intersection of two sets
4
votes
1answer
766 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 ...
0
votes
2answers
352 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 ...
0
votes
0answers
119 views
Responses Spreadsheet for Google forms: answers are not pasted properly
I created a Google Form and I have been tweaking the Responses spreadsheet to automatically formulate some statistics (you have seen this in my previous question).
Now, the spreadsheet has a weird ...
0
votes
0answers
340 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
124 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 | ...
3
votes
2answers
338 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
85 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
173 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 ...
7
votes
1answer
210 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| ...
2
votes
2answers
302 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
273 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 ...
0
votes
0answers
41 views
How can I show the freeze order on the published file?
I created an Excel file on Google Drive, with the intention to embed it on my website. I need to freeze 3 columns in the sheet, save it and publish the file. But the file was published without fixed ...
2
votes
2answers
295 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 ...
2
votes
0answers
33 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
1k 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
192 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
704 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 ...
2
votes
1answer
2k 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 ...
1
vote
1answer
206 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.
3
votes
1answer
246 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 |
| ...
1
vote
1answer
496 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
181 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)) ...
1
vote
0answers
141 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
149 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
215 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 ...
0
votes
0answers
38 views
Search and fetch the next cell in an array within one row of data in Google Spreadsheet
Trying to search and fetch the next cell in an array within only one row of data in Google Spreadsheet.
Vlookup and hlookup don't work cause it is getting the data from next column or next row instead ...
1
vote
1answer
138 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
538 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 ...
0
votes
0answers
9 views
How do I maintain text formatting when the word or sentence exceeds the cell length? [duplicate]
Possible Duplicate:
Google Spreadsheet formatting a cell with wordwrap without switching modes?
Maybe the answer to this question as been answered and I just didn't see it, but I have some ...
2
votes
2answers
437 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
669 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
14 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 ...
3
votes
1answer
99 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 ...
1
vote
2answers
112 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.
...
1
vote
1answer
101 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
1answer
497 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
608 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
0answers
167 views
Converting and Excel to Google Spreadsheet
I am trying to convert an Excel Spreadsheet to a Google Spreadsheet, but after it is converted, the document is smaller in size. How can it get it to be the exact size that I uploaded?
0
votes
1answer
119 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
136 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 ...
0
votes
1answer
154 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
866 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
1answer
213 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
117 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
162 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. ...
1
vote
0answers
133 views
Google spreadsheets and sorting protected range
I've been trying to make the spreadsheet automatically sort some data, and after some searching I found this link..
it's working just perfect with my permissions as the owner but when any of my ...
0
votes
1answer
94 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
93 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
219 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?
