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

learn more… | top users | synonyms (1)

1
vote
2answers
184 views

How to zero out cell if filtered sum is less than 0

I currently have the exact function set up here: Summing a column based on another column in Google Spreadsheet I would like to know how to make the value 0 if the real sum is less than 0. Any help? ...
1
vote
2answers
95 views

Possible To Get Average Function For Accumulative Input?

I am trying to figure out an average function for my Google spreadsheet, but I am not sure if it is possible, see screenshot: I could not fit all the spreadsheet in the pic, but the column A goes ...
1
vote
2answers
587 views

Formula to populate one column based on the value in another one

I would like to populate a column R with the value YES, if another column N has a value of NO.
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
1answer
404 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 ...
1
vote
1answer
589 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 ...
1
vote
1answer
102 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 ...
1
vote
1answer
571 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 ...
1
vote
1answer
124 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 ...
1
vote
1answer
63 views

Add columns content if the header is selected as 1 or 0

Trying to add across horizontally - if the column header is selected as being 1 - then the values of the column are to be added to the adjacent one that has 1 as a header. Ff the column header is ...
1
vote
1answer
62 views

Finding the most important contributor to a column in Google Spreadsheet

I have a column in a google spreadsheet that's calculated as a function of a few other variables (columns). Is there a function that I can use to find which of the variables has the most influence ...
1
vote
2answers
646 views

GDocs: Send Email Notification Based on Timeframe and a Given Email Address From a Cell?

How specifically does the code found in this article need to be modified to allow for a specified email address column to inform who the reminder should go to? I have an example spreadsheet here with ...
1
vote
1answer
447 views

Google spreadsheets—sum cells from another row based on a cell value

My current code is: (SUM(INDIRECT(ADDRESS(ROW()-(I4:I- 1);COLUMN()-4;4)&":"&ADDRESS(ROW();COLUMN()-4;4)))*( ( J4:J ) / 10 )))) I want to sum cells from Gx – Gy but range is determed by cell ...
1
vote
1answer
166 views

Run a custom script more than 20 times in Google Spreadsheet

I need a way of running a custom script more than 20 times in my spreadsheet. I'm creating a class assessment spreadsheet that will be used as a master across my school, and it needs to retrieve the ...
1
vote
1answer
964 views

Dependent validation in Google Spreadsheets

Here's what I'd like to do. The first selection is Subject (as in a school course). There are 5 subjects. Then there are three sub-selections in drop down lists for each subject: Subject Test Type ...
1
vote
1answer
82 views

Google Spreadsheets Validation list with Label

Thanks in advance. I have just started using Google Spreadsheets so am very new to it's ways! So far it looks incredibly powerful! I am using some data in a sheet called Jobs as a reference to ...
1
vote
2answers
1k views

Can I copy row from one Google Spreadsheet to another based on certain criteria

I have a spreadsheet to track student loan outs: Column A is the student name Column B is how many loan outs they have received Column C is the class period Column D is the teacher name ...
1
vote
1answer
234 views

Google Docs: How to find and display matching value on new sheet based on two criteria

I have created a demo spreadsheet to help explain http://goo.gl/1TcQrW (This is a small set of example data, the real set has more values [and the data may or may not be there from week to week]) I ...
1
vote
1answer
2k views

Auto populate data in Google Spreadsheet from data in separate spreadsheet?

So, I'm in the situation where I have a Google Spreadsheet with people's names, phone numbers, addresses, etc. Is it possible to, from another spreadsheet (not sheet, different workbook), search the ...
1
vote
1answer
216 views

How can I speed up my Google Spreadsheet Script?

I am somewhat new to JavaScript and very new to using it in Google Spreadsheets. I have read that fewer calls to Google Spreadsheet API speeds things up, but I feel like I have as few as possible at ...
1
vote
1answer
265 views

Duplicate check while creating Google Contact with Google Apps Script

I have the following code which creates Google Contacts from a Spreadsheet. The problem is whenever I run the code it creates contacts but has no functionality to check if the same Google Contact ...
1
vote
1answer
457 views

Google Spreadsheets function to copy array

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently. An example of when this is useful: you have a worksheet of raw data, and another worksheet ...
1
vote
1answer
468 views

Changing an onEdit function to run onOpen

I have the function below (found online and adapted and it works) that is an on-edit function, but I want it to run/trigger when an import is done OR on open, as the data will not really be edited it ...
1
vote
1answer
137 views

Is it possible to use the importHTML function with a concatenated URL?

What I'm basically trying to do is use a URL previously made with the CONCATENATE function in an IMPORTHTML function. When I try importHTML (H1, "table", 0) Where H1 is the cell with the ...
1
vote
1answer
847 views

Copy subset of rows from one sheet to another, based on value in column

I have a sheet with accounts, consisting of 3 columns: Accountnr Name Category This sheet is not nescessarily sorted on any column.The Category is one of three possible values. I want to fill a ...
1
vote
2answers
487 views

REGEXEXTRACT and French accents in Google Spreadsheets

I have the following formula: =REGEXEXTRACT(source!A2,"^[\w\s\À-ÿ]+") where source!A2="Ancien Marché Saint-Jacques (1125, rue Ontario Est)" and I would like to extract Ancien Marché ...
1
vote
1answer
425 views

Create New Google Spreadsheet with timestamp

I'm trying to create a new spreadsheet once we select the specific function from the custom menu item that's selected. I want the new spreadsheet to be "Sheet" & "Timestamp". Can anyone point me ...
1
vote
1answer
584 views

How to make a simple two column data chart in google spreadsheet? (simple case but doesn't seem to work)

I have a column with dates and a column with an amount of money. I just want to make the simplest chart possible, a line chart that shows money as a function of time, so I can see in what dates I tend ...
1
vote
1answer
251 views

How can I set the increment value for Google Spreadsheets to auto-increment?

I have a series of cells referencing other cells: | Row | a | b | col B shows | |-------------------------------| | 1 | 1 | =a2 | 2 | | 2 | 2 | =a4 | 4 | | 3 | ...
1
vote
1answer
926 views

Google Spreadsheets - how to add carriage returns to CSV downloads

In reference to this question: Google Spreadsheet downloaded as CSV comes out on 1 line? Is there anyway to have a downloaded Google Spreadsheet incorporate carriage returns and linefeeds such as if ...
1
vote
1answer
139 views

How Many Ways To Sort in Google Spreadsheets

When I'm working in a Google Spreadsheet, I always use the same routine to perform a sort. Are there more ways to sort, that lead to Rome?
1
vote
2answers
1k views

Paste format only in Google Spreadsheets with Chrome as the browser

I want a working keyboard shortcut for Google Spreadsheets, that lets me paste just the formatting. Google says the shortcut for copying just the formatting is Cmd+Alt+C, and pasting just the ...
1
vote
2answers
232 views

Is there a way to speed up the auto-increment routine in Google Spreadsheets?

The solutions that have been provided for this question does the job, but when more people submit data at the same time, things go wrong. The same number is used in multiple rows. Is there a way to ...
1
vote
1answer
40 views

Google Spreedsheets Maths Date

In column A, I have today's date 18/06/2013 and I want to add 25 days to it, but because I have a list of 100 dates (all different) I need to do the same thing that =DATE(2013,06,18)+25 would do but ...
1
vote
1answer
1k views

INDEX-MATCH to get a MATCH and use that MATCH in a second INDEX-MATCH

I am trying moving from an access database to google spreadsheets. In my workbook, I have 3 google sheets: merged_data (merge all data) studentsANDemployers (studentID, student_name, employerID) ...
1
vote
1answer
482 views

Use specific currency symbol in Google Spreadsheets

'₴' is a currency sign used for the Ukrainian Hryvnia. It's in Unicode since 2004. I want to use it in spreadsheets as a sign for money values, but Google Spreadsheets doesn't understand it as a ...
1
vote
1answer
459 views

How to zip two columns together in a Google Spreadsheet [duplicate]

For example columns are setup as: A | B ----- 1 | a 2 | b 3 | c 4 | d 5 | e I want to be able to make column C be like this: C --- 1 2 3 4 5 a b c d e via formula. The two columns ...
1
vote
1answer
201 views

Google Spreadsheets Messes Up Comments When Sorted [duplicate]

I added comments to a bunch of cells. When I then sorted the cells, the comments did not follow their original cell and now was connected to the new cell. This is a massive problem and now my ...
1
vote
1answer
180 views

Google Spreadsheets Filtering data in another page and changing data in the original

I have a lot of data that I need to filter. I did it in another page and that's good... but now I need to put some values in that page... and transfer that data to the original. This is the formula ...
1
vote
1answer
220 views

How to have Google Spreadsheets automatically complete cells with previously used data

I'm using a Google Spreadsheet to fill in my mileage. Since I'm using many addresses more than once, I want to make the process of filling in easier by (like Excel or Open Office) having the ...
1
vote
1answer
77 views

Share individual copies of a Google spreadsheet

I created a Google Spreadsheet file which I want to share with my coworkers so each one has his own copy. So far the only way I found is to share the file, however these doesn't work for me because ...
1
vote
2answers
1k views

Google spreadsheet connect with database and fetch data to cells

I have been wondering about this for a while. We are using those google spreadsheet a lot with my colleagues and the thing is that we always have to write down data that we already have in our MySQL ...
1
vote
1answer
1k views

Exclude cell from sum in Google Spreadsheet

I am using my Google Spreadsheet to keep tabs of my incomming and outgoing money. I've made a row with ~19 cells in it, in those 19 cells I have the monthly what comes in and what goes out. At the end ...
1
vote
1answer
253 views

Conditional formatting for dates in Google sheets

I work at a language school and I have a column giving the students expected arrival date. I want to set conditional formatting to: Highlight in red any date that is older than 1 week Highlight in ...
1
vote
1answer
6k views

How do I automatically update a cell in Google Spreadsheets to the most recently entered in the same row?

I am trying to have one column that will show the most recent thing i entered in the row. Example: I want B2 to be the same as D2 but once I enter something in E2 I want B2 to display that. And then ...
1
vote
1answer
141 views

Finding and removing periods in Google Spreadsheet

I'm using a Google Spreadsheet and trying to create a formula to remove a period and anything that comes before it in a cell. For example: idontwantthis.iwantthis I have used this in the past: ...
1
vote
1answer
970 views

Sorting Google spreadsheet by month and day but not year

I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened. If you sort by the date normally, ...
1
vote
1answer
239 views

2 axis line chart in Google Spreadsheets

I've set up a Google Spreadsheet to compare one set of stats against another. The stats are for traffic vs. an action the actions only happen once a day and not every day were as the traffic has more ...
1
vote
3answers
4k views

How to auto increment a field in Google Spreadsheet

In my spreadsheet I have a column called S+ No. How to auto increment this for each row, like: S1,S2,S3,S4 In short how to make it dynamic.
1
vote
1answer
1k views

How can I apply a script to only one specific sheet within a spreadsheet

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A function onEdit(event){ var sheet = ...