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

learn more… | top users | synonyms (2)

2
votes
3answers
4k 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
1k 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
118 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 ...
3
votes
1answer
1k 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 ...
2
votes
2answers
3k views

Calculate days between now and a future date

I need to calculate how many days are left from today to a certain date in the future in a Google Spreadsheet. How can I do that?
3
votes
1answer
2k 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 ...
5
votes
2answers
6k views

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

In Google Sheets, 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
1k 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 ...
2
votes
1answer
1k 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 ...
5
votes
1answer
4k 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 ...
2
votes
2answers
137 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
27 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 ...
2
votes
2answers
2k 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 ...
3
votes
2answers
137 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
6k 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 ...
4
votes
2answers
15k 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
253 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
1answer
590 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, let'...
2
votes
1answer
102 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 that are years. I started with this formula, but that didn't work: =COUNTA(UNIQUE(...
0
votes
1answer
1k 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. ...
3
votes
2answers
2k 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 ...
2
votes
1answer
680 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
97 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.
2
votes
1answer
308 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 ' " &text(TODAY()-15;...
3
votes
1answer
88 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
102 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
2answers
3k 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: =query(ImportRange("...
3
votes
1answer
70 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
1answer
202 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 that involves a small pop-up that says "Go to link..." However, is there a way where the user of the sheet can directly click on the link ...
4
votes
1answer
125 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, ...
3
votes
1answer
114 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
141 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 ...
5
votes
3answers
3k 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?
9
votes
3answers
4k views

Default sheet in Google 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
582 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
388 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
204 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 ...
2
votes
1answer
885 views

Google Spreadsheets: Unique Values From a Matrix

I have a spreadsheet that looks like this: Column A | Column B | An unimportant value 1 | Value 1, Value 2 2 | 3 | Value 3 4 | Value 3, Value 1 | Don't ...
1
vote
0answers
26 views

Formula syntax to use a cell data as reference to another cell in a Google spreadsheet [duplicate]

Say I have the cell A1 which contains the number "13". I want to use that number to refer to a specific cell doing something like ='Items'!C"A1" which would mean ='Items'!C13. What is the proper way ...
3
votes
1answer
102 views

Remove word values from the output of UNIQUE(SourceArray) in a Google spreadsheet

I am trying to list all the "other" options of a Google form. As such, I am using UNIQUE to list all results from a row so: =UNIQUE('Form Responses'!S2:S) I would the want to exclude the following ...
3
votes
1answer
359 views

How to update cell value to max if value is smaller than other cell in Google Spreadsheets

I would like cell A to have the max value that another cell B has ever had. How can I do that? Cell B changes value dynamically according to a =GoogleFinance() formula (every few seconds or so).
6
votes
1answer
1k views

In Google Spreadsheet, how to left align currency symbol but right align number?

How to left align dollar sign and right align the number, like Excel currency format? For example: $ 3,546 $ 44,345 Also, is there a way to create a custom format?
3
votes
1answer
2k views

How to lock a cell based on date in Google Spreadsheets

I have set up a simple spreadsheet with cells containing a drop down list of Yes and No. I want to lock these cells based on date, so that all the people who I am sharing the sheet with will not be ...
0
votes
2answers
129 views

Spreadsheet: function to watch row for changes

Is there a spreadsheet function that can watch a row and update a cell with the current date when any cell in that row changes? If not for the entire row then perhaps just watch a single cell?
6
votes
2answers
621 views

Custom data transpose?

I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than ...
3
votes
1answer
550 views

How to anchor all the cells on the page in Google Spreadsheet

To anchor cells in a Google spreadsheet so that you can drag the values/cells/formula, with one or more of the linked cells/values remaining constant you can follow these steps: http://productforums....
4
votes
1answer
2k views

Get Row-Number of a MAX(Range) Value?

I have a spreadsheet with several items, their volumes and margins. I have no problem in finding the maximum margin with =MAX(Range). What I need to find is the row number of that value. Let's say I ...
2
votes
1answer
44 views

Can I make modifiable part of the web address passed through in a Google Spreadsheets =ImportHtml function?

Wondering if there is a way to modify part of the web address (passed through in double quotes) in a =ImportHtml function in Google Spreadsheets? eg. www.schoolgrades.com/tom www.schoolgrades.com/...
1
vote
1answer
1k views

How to use triggers with functions that require parameters from the spreadsheet?

I found a simple and great solution to retrieve JSON into a Google spreadsheet. I want the function ImportJSON to update regularly via a trigger (event or time-based, I still haven't decided what I ...
2
votes
1answer
8k views

Google Spreadsheet - How to filter, SUM and display multiple values with multiple conditions added daily?

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by ...