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

learn more… | top users | synonyms (2)

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
119 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
4k 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 ...
3
votes
2answers
9k 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
216 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
504 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, ...
2
votes
1answer
81 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: ...
0
votes
1answer
798 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
1k 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
586 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.
2
votes
1answer
253 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
80 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
100 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: ...
3
votes
1answer
69 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
131 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
121 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
113 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
131 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
3k 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
503 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
302 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
193 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
798 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
95 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
287 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).
5
votes
1answer
808 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
1k 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
119 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
528 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
457 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: ...
3
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
42 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 ...
1
vote
1answer
886 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
7k 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 ...
7
votes
1answer
10k views

Count the number of words in a string in Google Spreadsheet

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-build Google spreadsheet function I may use?
0
votes
2answers
292 views

Calculating time worked per day

How do I add time like 8:20am-12:00pm and then after lunch break 12:30pm-5:02pm to end of day to get hours worked per day? I was using =(B2-A2*24)+(D2-A2*24) but that doesn't work
3
votes
1answer
2k views

How can you add a conditional statement to a =QUERY function

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...
4
votes
2answers
1k views

How do I automatically expand a =QUERY when receiving submissions from a Google Form?

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the 'responses sheet'. The data being added to the 'responses ...
0
votes
1answer
1k views

Generating charts from Google Form Data via Google Spreadsheets

I have two colums that are generated from a google form: I would like to create two pie charts, one for City A and one for City B, showing the percentages of male and females from those cities. I ...
6
votes
1answer
2k views

Publish spreadsheet as Atom/RSS not possible in Google Apps for Business

I am trying to publish a Google spreadsheet as an RSS feed using Google Chrome. When I go to File → Publish to web and then go to Get a link to the published data, the option Atom/RSS feed is not ...
1
vote
1answer
122 views

Is there a way to count how many entries in a given column have a particular value?

I have a spreadsheet which keeps track of duels between two players in a game. Each row has the number of the match and the winner, given by their initial, X or Y. I want to know if it's possible to ...
1
vote
0answers
55 views

Adding and deleting sheets in Google Spreadsheets

How can I restrict users from adding new and deleting existing sheets in Google Spreadsheets? Only the owner of the spreadsheet should be able to do that. Other users should not be able to do that.
1
vote
0answers
18 views

Find out the folder path of the current doc in Google Docs [duplicate]

I am able to search a file and open it. But I am not able to locate the folder which has this file. There is not Properties in the File menu.
2
votes
1answer
587 views

Change contents of cell based on date range in Google spreadsheet

I’m trying to get a cell to change its value based on a date range. Let's say I have this list: A B date1 10 date2 25 date3 50 date4 85 On date1 I will have 10 items, date2 I ...
1
vote
1answer
154 views

How do I copy rows with certain criteria from multiple spreadsheets into one spreadsheet

I made a Google Form for assigning a task to myself or a colleague. For Example: spreadsheet name: Science, Column 1=Name 2=task 3=due date spreadsheet name: Mathematics, Column 1=Name 2=task ...