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

learn more… | top users | synonyms (1)

1
vote
1answer
85 views

Sum every 9th row starting from the 4th row in Google spreadsheet

I have a column where there is a number every 9th row. The problem is that my first value starts on 4th row then all the next ones every 9th row. So far I tried this: ...
1
vote
1answer
60 views

IF OR Google Spreadsheets Formula Problem

I'm getting an error back for this formula. I've tried with commas, without space, short formula and I still can't seem to get it to work. Here's the formula that I'm using: =IF(OR(C3 = "Jenna"; ...
1
vote
2answers
2k views

Check if Column contains value, when found return contents of another column in that row

I'm using a Google Drive spreadsheet to organize guests I want to invite to my wedding. On one sheet I want all my guests and information relating to them (are they friend/family? Are they an ...
0
votes
0answers
90 views

“ImportXML” function in Google spreadsheet for “answers.yahoo” site

Please help me understand why this formula =importXML("http://answers.yahoo.com/search/search_result?fr=uh3_answers_vert_gs&type=2button&p=cloud","//ul[@id='yan-questions']") is not ...
1
vote
0answers
116 views

SUM(SUMIFS Array in Google Spreadsheet

Now that Google Spreadsheets support SUMIFS I'm trying to import an expense tracking spreadsheet but I've run into a problem with a formula. Here is the current code: =SUM(SUMIFS('RE ...
0
votes
0answers
56 views

Error in Query function with aggregate function in two column & Pivot

I have an error while using query function which I have used in “Solution” Sheet. Below is the function that I have used: =QUERY(Problem!A6:G3493,"Select C,sum(F),Sum(G) group by C Pivot D label C ...
0
votes
1answer
131 views

Creating a document (e.g,, MS Word) attachment in Gmail from a spreadsheet script

I've created a document file in the spreadsheet script. What I want to do is, from the script, create an email message with the document file (NOT a PDF) as a Gmail attachment. I spent about 2 days ...
1
vote
2answers
1k views

Script to automatically change text value

In GDocs under Tools > Preferences, I can define automatic corrections, so that if I type l*, it spells low, h* -> high, etc. In Google Spreadsheet there is nothing like that. Can I do that with ...
1
vote
1answer
390 views

Limiting Range in OnEdit Timestamp Script in Google Spreadsheets

I would like to be able to limit the range of cells being assessed by this script to the first column. Apply the timestamp to the last column and only have the script run on a specific sheet "Short ...
4
votes
2answers
210 views

Google Sheets - How can I use row values to index into another sheet?

I have an arbitrary google spreadsheet Data. alpha | 0.1 beta | 0.2 gamma | 0.3 delta | 0.4 epsilon | 0.5 And another sheet in the same file, Data Subset that holds an arbitrary ...
0
votes
1answer
683 views

How to apply formulas, validation to newly added row (using add more rows at the bottom not using insert)?

I have formulas and validation in columns D, E, L, M and N. I want these formulas and validation to be automatic whenever I add rows using the option "Add more rows at the bottom". Not using insert ...
2
votes
2answers
2k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
0
votes
1answer
408 views

How can I record the date/time when a new row is added?

I want to add a date value when a new row is added in Google spreadsheet. I have seen the script on Add a 'creation date' value when a new line is edited, but I was not able to modify the code to my ...
1
vote
2answers
73 views

What is the best approach to adding additional costs to items in Google Spreadsheets?

In Google Drive I'm keeping a spreadsheet of everything I spend money on. I have a heading for the cost but alone that's insufficient. For example, if I tip after a meal I really want to have that ...
2
votes
1answer
398 views

Hover to see value on a chart

I have a Google Spreadsheet on Google Drive, which until today I was able to see a value when I hovered on the dot with, but now it's not working anymore. Unfortunately I can't remember if I changed ...
3
votes
1answer
1k views

Google Spreadsheets Scripts Gallery menu item missing

How do we access the Scripts Gallery in Google Spreadsheets? Documentation around the web says that it should be in Tools -> Scripts Gallery. But I only see Script Manager and Script Editor. Going ...
2
votes
1answer
2k views

How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows

In google docs, I have the following data | A | B | C | ... | N | TOTALS | 1 | 123| 425| 324| ... | 234| =SUM(A1:N1) | 2 | 123| 425| 324| ... | 234| =SUM(A2:N2) | ...
1
vote
1answer
690 views

Is it possible to feed multiple form drop down lists?

I am using Tom Horwood's code which has worked for me for updating one drop-down. But my form has multiple drop-down lists which I would like to make dynamic. Is this possible? Tom Horwood's code ...
1
vote
1answer
558 views

Get value of cell based on the value of another cell

I'm looking for help on the following : Exemple sheet I need the word Frisbee to be put in the green cell Any idea ? The value researched will always be text, and depending on a date. Edit : Just ...
0
votes
0answers
55 views

Auto receipt filler

I have a spreadsheet that has two sheets, in which I pull data from for making printable receipts. At this point, I am doing it manually by copying the data from the first and second sheet and putting ...
5
votes
1answer
2k views

Why doesn't “Custom number format” show up in Google Sheets (Google Spreadsheets)?

According to this Google help article, I should be able to do the following to apply custom number formatting in a Google Sheets document. Highlight the data you want to format. Click the Format ...
1
vote
2answers
70 views

Google Spreadsheet - periodically stamp sheet contents

As the title, is there a way to have a "print" of the content of a sheet periodically? i.a., I'd like to have a trigger that every Monday at 08.15 A.M. copy the contents of a group of a cell of a ...
0
votes
1answer
26 views

IF Formula where X = an unidentified (but stable) #

I really think this should be simple - but I am stumped. Please see image. All I want is that my clients be able to put an "x" in the tasks they want performed. Each has an associated cost, which I ...
1
vote
1answer
700 views

Multiple views of master spreadsheet that allow editing of master data

I have a monster spreadsheet with lots of contacts in it. Each contact is a row, with different attributes as columns (first_name, last_name, number, etc.). There are a hundreds columns—i.e., too many ...
2
votes
1answer
67 views

Counting the number of matches between an “answer key” row and a “student response” row

I have a row containing an answer key for a multiple choice exam and then below that a number of rows containing the answers from students. A very simple question: how can I count the number of ...
1
vote
2answers
1k views

Using combined IF THEN with IMPORTRANGE formula in Google Spreadsheets

I am looking to understand what the correct syntax would be for a formula for a Google Spreadsheet that would use the IF THEN function inside of an IMPORTRANGE formula. See the two workbooks I ...
2
votes
2answers
2k views

How do I merge two columns together in Google Spreadsheets? [duplicate]

I have two columns [first name] [last name] I need to merge them to one column to see [first name last name]
1
vote
1answer
119 views

Pulling collumn from sheet 2 & importing into another workbook [duplicate]

So I need to find the formula to pull a column out of one page of my Google Docs spreadsheet and insert it into a separate workbook. Here are some more details: all rows from column C (part number) ...
1
vote
0answers
195 views

Repeat an FILTER or ARRAYFORMULA n times in Google Spreadsheets?

I would like to repeat an ARRAYFORMULA or FILTER n times. Let's assume I have A 1 a 2 b 3 c Now I set =ARRAYFORMULA(A1:A3) and I get the same output. But now I want it to repeat n times, ...
1
vote
1answer
44 views

How to COUNTIF cells are years in Google Spreadsheet?

I have several dates in let's say A1:A with this syntax: dd/mm/yyyy and I would like to COUNTIF the cells are years. I had this first: =COUNTA(UNIQUE(YEAR(A1:A))). But that didn't work.
0
votes
1answer
133 views

List of Dates of Current & Previous Month in Google Spreadsheets

I want to have the dates of the current month and the previous month automatically get populated. So I'm using =MONTH(NOW()) to get the current month and =MONTH(NOW())-1 to get the previous month. ...
0
votes
1answer
277 views

Google Spreadsheets Query( “where a matches '/[A-Z+]/' ”) condition

According to Google Query documentation: matches - A (preg) regular expression match. haystack matches needle is true if the regular expression in needle matches haystack. Examples: where ...
1
vote
1answer
205 views

Google docs spreadsheet - limit values to other ranges

I'm trying to create a docs spreadsheet that defines various objects, and these objects sometimes reference each other (equivalent to SQL foreign key). Is there a way that I can limit the values of a ...
3
votes
0answers
95 views

Color entire row based on the status in Column L [duplicate]

How can I make my spreadsheet look like in the picture below? Starting row 5, I want to color the entire row based on the status in Column L.
1
vote
0answers
113 views

Formatting a Google Docs spreadsheet (sorting multiple entries, linking tabs, incrementing numbers)

So, I'm trying to work with this spreadsheet I've created. I was wondering … is there a way to add a button of some kind, so that people can choose how each group is sorted? So they could sort by ...
2
votes
1answer
126 views

How to make a query with Google Spreadsheet, that returns items from past 15 days

How to make a query with Google Spreadsheet, that checks for a date within a certain range. I have tried using: =QUERY('Noah''s Weight'!A:D, "select * where A > date ' " ...
3
votes
1answer
56 views

Edate cannot have nested function call?

Why is it that this function does not display a date in Google Spreadsheets? =edate(today(),6) I am trying to display the date that is 6 months from today. Instead, the cell only shows a value like ...
2
votes
1answer
79 views

Google spreadsheet column to have same function throughout column

I have three columns. Two columns have values in them. In the third column I want to have each row be a percentage of the other two values in that row (ex. =N2/L2). How can I have this third column ...
2
votes
1answer
2k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: ...
0
votes
0answers
54 views

Import Google SpreadSheet data into Google Adwords via a script

Google Adwords data can be exported to a Google SpreadSheet, by using a script. How can I import Google SpreadSheet data into Google Adwards, with the usage of a script? I've gone through the API ...
3
votes
1answer
58 views

Filter by the sum of two cells in Google Spreadsheet

Let's say we have some data in a spreadsheet like: A B C Price Quantity Genre 20 2500 Car 10 1000 Car 10 2500 Bike I can filter ...
2
votes
0answers
69 views

Is there another way to click on the hyperlink in a Google Spreadsheet?

I am aware of the process to click a link on the spreadsheet. However, is there a way where the user of the sheet can directly click on the link? I am trying to prepare a decision tree where, once a ...
4
votes
1answer
97 views

Counting blank cells based on the information in a related cell

I'm trying to use information from a table to display a count of blank cells based on the type of data in the table. See my example below - I want to count how many of each fruit I'm missing, ...
2
votes
1answer
89 views

Google spreadsheet: Lookup values between two tables

I have two tables, people and jobs, on two separate sheets. How do I find the names of the people who: Are not in their dream jobs Their dream job is available in the jobs table? People | | A ...
1
vote
1answer
98 views

Counting number of cells with certain word if another cell in that row is not blank

I have 2 columns, "machine type" and "install date". What I want to do is count the number of machines per type once they are installed. So if there were 3 "Machinetype1" installed 11/29/13, I want ...
3
votes
3answers
986 views

How automatically convert data in Google spreadsheet?

I have date information like this:February 1, 2014. I want to transform them into 2/1/2014 in Google spreadsheet. I have hundreds of dates so that I cannot transform them by hand. What should I do?
6
votes
2answers
816 views

Default sheet in Google Docs spreadsheet

Is it possible to mark a sheet as "Default" so that if anyone opens a Google Docs spreadsheet, the corresponding sheet is opened instead of the first one?
1
vote
1answer
100 views

Sorting by the value of the formula in a Google spreadsheet

I want to sort data by the value of the formula. The data is generated by a formula. I want to sort the data (and related rows) by the value of the formula output not the formula. I cannot do this. ...
4
votes
1answer
140 views

Link in cell using URL from another cell

On one worksheet I have a table of data that includes an item name, person name, price, and a URL. On an overview worksheet in question I use the FILTER formula to list the items by person using the ...
1
vote
2answers
156 views

VLOOKUP or INDEX+MATCH

I have an array of values: A B C 1 TypeID MaterialTypeID Quantity 2 18 34 256 3 18 35 512 4 20 34 386 5 20 36 773 ...