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

learn more… | top users | synonyms (2)

1
vote
1answer
375 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
189 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
64 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
92 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
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
64 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
79 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
108 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
103 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
117 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 ...
4
votes
3answers
2k 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?
7
votes
2answers
2k 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
291 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
191 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
170 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
648 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
66 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 ...
-1
votes
1answer
91 views

Editing a Google Docs response sheet

I want a response from a 2nd form to be able to update (or add a new cell) in an existing form. Is it possible? What i want to achieve is i show only some of the information to a third party, and i ...
3
votes
1answer
185 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).
4
votes
1answer
404 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
582 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
101 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
374 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 ...
0
votes
0answers
515 views

Prefilled Google Forms with Google Spreadsheet Data

I'm looking for a way to 'pre-fill' a Google Form with specific data from a Google Spreadsheet. The form will have the same 'standard' questions for everyone, but the data in the first two question ...
3
votes
1answer
297 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
1k 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 ...
0
votes
0answers
136 views

Google Spreadsheets formula error

I have the following formula in a sheet that works in Excel but comes up with an error in Google Spreadsheets. =(IF(A6<=TODAY(), INDEX($AS$2:$AU$12,MATCH(A6,$AS$2:$AS$12,1), ...
1
vote
1answer
698 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
5k 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 ...
3
votes
1answer
5k 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
189 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 ...
3
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
826 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
0answers
48 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
17 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
461 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
144 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 ...
2
votes
2answers
123 views

How to do sum(max(…))?

Here is my raw data: Site IP Visits Age (in hours) 1 uu.com 1.2.3.4 10 3 2 vv.com 2.9.7.5 2 4 3 kk.com 2.9.7.5 8 24 4 uu.com ...
1
vote
1answer
442 views

Index-Match on Arrayformula

I'm making a database in google drive. In one column I have abbreviations of a number of departments someone is in, but people can be linked to multiple departments. The abbreviations are ...
2
votes
1answer
978 views

How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...
1
vote
1answer
1k views

Creating duplicate Google spreadsheets that filter data from a master sheet and update rows on master sheet

Basically, I have a huge list of data on Google Docs spreadsheets, and a bunch of people that read it. They are not tech savvy. I am trying to create duplicate sheets on a Google spreadsheet that are ...
0
votes
0answers
74 views

Spreadsheet Data into a Form

I am building a simulation (a "create your own adventure") for my students in which they use a Google Form to select into which of 5 groups they would place 25 first graders (Round 1). I get the ...
2
votes
1answer
237 views

IF statement with ranges

How do I make it so that if any number in the range C6:D11 on PAGE is less than zero, output "Some words" and if not, output "Some other words"? I tried using =IF(PAGE!C6:D11 < 0, "Some words", ...
2
votes
2answers
794 views

How to automatically add spaces in between each character in a text string?

Teachers I work with enter 4 scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces. How can I separate those characters so as to be able to create a chart of ...
0
votes
1answer
77 views

What is the correct syntax for the IF function?

I'm trying to make a cell output "Some words" if the number cell in B10 in a page named "Page" is greater than or equal to 3. If not, it should output "Some other words". I tried =IF("'PAGE'!B10≤3", ...