Part of the Google Docs suite, a web app that allows you to create and edit worksheets
2
votes
1answer
159 views
Show 'sum' when multiple cells selected in Google Spreadsheet
If I select multiple cells in a Google Spreadsheet,
sum or count appears at the lowerright corner according to the data type.
Sum is shown for numbers, and count is shown for characters, dates or ...
2
votes
1answer
253 views
Google SpreadSheet conditional formatting based on number of characters
In Google Spreadsheet, is there a way to conditionally format a cell if it has more than 30 characters?
0
votes
0answers
107 views
Apply conditional formatting rules to Google spreadsheet column past set limit (10)
Limit to conditional formatting appears to be 10 per column. Any work around except creating another column next to it for those additional rules or functions, then copy/paste data from one column to ...
0
votes
1answer
463 views
Password protected online database or spreadsheet
I have a client that wants a simple online database or spreadsheet solution.
Requirements
Custom fields and field types (text, images)
CRUD contents through a web GUI
Password protected (single ...
1
vote
1answer
185 views
Date format in spreadsheet (google doc)
I want to create spreadsheets to record the money I spend each year.
Each file is named as Cost-2012, Cost-2013,...etc.
Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., ...
3
votes
1answer
163 views
Google Apps Spreadheet SUMPRODUCT issue
I have a table with the headings
Status | Currency | Total
In the rows I have a variation of statuses and currencies.
Using Google Spreadsheet I added this:
...
1
vote
1answer
116 views
Sorting Google spreadsheet by month and day but not year
I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened. If you sort by the date normally, ...
0
votes
0answers
92 views
when I importrange from one Google SpreadSheet to another, why are my dates screwed up? [closed]
The dates in the first workbook are entered as 1/1/2013.
When I import the entire sheet into a different workbook, the dates are shown as a day earlier (1/1/2013 would be shown as 12/31/2012).
Really ...
0
votes
0answers
230 views
Use Google Apps Script to Parse Google Spreadsheet cell based on date
I would like to use a google apps script to parse a spreadsheet containing 2 columns:
|--Date--|--Schedule--|
|-3/4/13-|--Day 5, G--|
|-3/5/13-|--Day 6, F--|
I want to create a script that parses ...
2
votes
0answers
375 views
Google Spreadsheets: add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger
At the end of each row filled in, using a custom Google Form, I'm adding three more columns:
Date of creation
Unique ID
QR code (it is a list of computers)
Unique ID and QR code are unique. QR ...
2
votes
1answer
79 views
How get last added text from column into different sheet
How can I get the last added plain text from a column in sheet1, which also has empty rows, into a cell of a sheet2?
With help I managed to do this with the last added date, but with plain text I am ...
2
votes
1answer
197 views
Get last date from a column on a different sheet
I'm looking for help on the following:
How do I get the last date from a column in sheet1 returned in a cell of sheet2
I tried it with:
=FILTER(Sheet1!A:A ; ROW(A:A) =MAX( FILTER(ROW(A:A) ; ...
3
votes
1answer
106 views
In Google Spreadsheets how do I put in a formula in after a transpose?
In my spreadsheet the transpose formula fills in a deliberately variable number of column cells, how do I fill in a cell after a transpose formula? I've created a youtube video to demonstrate (click ...
1
vote
0answers
104 views
Sorting on a web-published Google Spreadsheet
I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc.
I would ...
0
votes
0answers
108 views
2 axis line chart in Google Spreadsheets
I've set up a Google Spreadsheet to compare one set of stats against another. The stats are for traffic vs. an action the actions only happen once a day and not every day were as the traffic has more ...
2
votes
1answer
73 views
How to make a ratio win:total with only integers and least terms
So basically I am trying to find a way to show a simple ratio of wins vs total games.
for example:
2 wins on 5 games would give me a ratio of 2:5
5 wins on 15 games: 1:3
8 wins on 18 ...
0
votes
0answers
131 views
focus a specific cell in Google Spreadsheet when a URL is clicked?
I have a questions regarding Google Spreadsheet remote entry.
We use Google Spreadsheets to maintain our inventory and we use QR labels attached to the boxes - we use an iPhone app to decode the QR ...
0
votes
0answers
110 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 ...
0
votes
0answers
27 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 ...
0
votes
0answers
22 views
How to paste as value in a given time with a formula (google sheets)
I have created an indicator and it changes every day. I want a record of it in a serie, to make a lines graphic, this is, in Google Spreadsheets.
One idea is to paste as values, with an "if" function ...
0
votes
0answers
231 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 ...
1
vote
1answer
120 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
613 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
286 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
108 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
238 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
116 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
252 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
69 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
149 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
195 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
194 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
223 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
40 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
237 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
32 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
665 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
177 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
563 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
1k 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
144 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
162 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
257 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
145 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
121 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
129 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
188 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
36 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
129 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
438 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 ...
