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

learn more… | top users | synonyms (1)

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
1k 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
67 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
395 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
60 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
857 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
1k 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
106 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
183 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
42 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
0answers
72 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
229 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
160 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
106 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
113 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
77 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
1k 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
51 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
53 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
66 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
89 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
87 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
81 views

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

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
687 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?
3
votes
2answers
527 views

Default sheet in Google docs spreadsheet

Is it possible to mark a sheet as "Default" so that if any one opens a Google doc spreadsheet the corresponding sheet is opened instead of the first one.
1
vote
1answer
82 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
125 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
154 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
321 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 ...
0
votes
0answers
43 views

Google Sheets Function to have a button take you to a specific sheet

So I followed the instructions to make a function as found here. But when I click the button I made, it takes me to the correct sheet but all the data is hidden. In this example I clicked the ...
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
40 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
66 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
114 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).
3
votes
0answers
142 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
173 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 ...
1
vote
1answer
57 views

Spreadsheet: function to watch row for changes

Is there a spreadsheet function (Google, Excel, Numbers, or otherwise) 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 ...
0
votes
0answers
140 views

Index Match Script for Google Spreadsheet

I'm making a scheduling database in Google Drive. On a second sheet in the same Spreadsheet I have all accounts listed in columns: I would like on the 1st sheet to type in an account number in ...
6
votes
2answers
199 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
245 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
0answers
134 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: ...
2
votes
1answer
666 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
40 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
23 views

Is it possible to link an editable Google Spreadsheet in to Google Coursebuilder?

Is it possible to link an editable Google Spreadsheet in to Google Coursebuilder? I see that I can add a Google Spreadsheet into the lesson body using the rich text editor and adding the gcb: Google ...
0
votes
0answers
89 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
460 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
2k 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 ...