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

learn more… | top users | synonyms (2)

1
vote
2answers
139 views

How can I merge into one cell, all the non-zero values in a row each concatenated with the column header?

I hope this example is clear. I have played with join and filter but can't get the Summary field to display as below: Row 1 is a list of subjects. Row 2 is a teacher's subject allocation ...
1
vote
1answer
302 views

How to send cell as argument for function?

I am trying to write a custom function for Google spreadsheets that checks the formatting of a given cell and acts accordingly. I know that I can give a string as argument and get it to work but then ...
1
vote
1answer
148 views

How to select a particular cell from an 'array formula' result?

Take this formula: =GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1)) This formula returns an 'array result', meaning it generates a little table over multiple cells, like this: The top-left cell ...
1
vote
2answers
397 views

Reflect changes in on sheet to another on Google spreadsheet

I have two sheets on a Google Spreadsheet. One of which is the copy of the other. Now what I want is, when I edit one sheet, the same edit must reflect on the other. How can I do that ?
1
vote
1answer
35 views

“Object not found” error when saving a Google Apps spreadsheet in Box after edit

The corporate entity that I work for is rolling out Box.com, and since we like the collaborative features of Google Apps, I am experimenting with the Box.com integration with Google Apps. Using the ...
1
vote
1answer
379 views

Google Spreadsheet SQL subquery “where not in”

I'm attempting to perform the following SQL command concept, but with two sheets in the same google spreadsheet document: select * from table1 where column1 not in (select column1 from table2) How ...
1
vote
1answer
47 views

How can I copy the lower triangle into the upper triangle?

I have a matrix in Google Drive Spreadsheets. Only the lower triangle is filled. I would like to copy the values of the lower triangle to the upper triangle. How can I achieve that? Example: ...
1
vote
1answer
293 views

How do I write a formula in Google Spreadsheets to to compare two cells and write answer into another cell

I am running into some obstacles while trying to design my ideal Google Spreadsheet. I am trying to do the following listed below and I am having a hard time. I was wondering if anyone can help ...
1
vote
1answer
86 views

Button in column C in each row

I have a spreadsheet with three columns and lots of rows. Column A has a numerical ID Column B is empty Column C is empty I want to have a button in each row in column C. When pressed, it should ...
1
vote
1answer
325 views

Formatting integer to Gold/Silver/Copper using custom number format

I have number in coppers: there are 100 coppers to a silver and 100 silvers to a gold I need the raw number to stay in the cell so it can be used in calculations. Ideal format: 1 ==> 1c 123 => 1s ...
1
vote
1answer
81 views

Need to Make this Code Sheet Name specific rather then the active sheet

I got this code on here but need to modify it so it only sees 3 sheets. 1 is Active Tickets. 2 is Closed Tickets. 3 is Pipeline. This is the code i currently have: function onEdit(event) { ...
1
vote
2answers
78 views

How can I copy part of a column (text) to another column in other sheet?

I'm trying to figure out how to duplicate part of the content of a column (text) to another column in other sheet. For example, I have column A in Sheet1 and I want to show only a word of this column ...
1
vote
1answer
315 views

Google Drive Spreadsheets: how to take form responses from one sheet to update the next?

References: Identifier number is the key field which we are wanting to use to link the two sheets together (this will also be referred to as "id") First sheet is the full list of identifiers and ...
1
vote
1answer
232 views

Return text from multiple cells on another sheet with linefeed if dates match

I have an example here of my spreadsheet. Basically my main page has a drop-down (via data validation) where you can toggle through any month and it responds by showing data for that month. What I ...
1
vote
1answer
92 views

Format a Cell with Script

I need to write a script to see if a cells value is found in an array, and if it is change the background color of that cell. Can't do this with "Conditional Formatting" as it needs to compare with ...
1
vote
1answer
133 views

SUMIF not working

I have values in column L and I'd like to SUM what is in column B IF the value in L is greater than 1600 Here is my formula: =SUMIF(L2:L,">1600",B2:B) I'm getting a result of 0 in the cell ...
1
vote
1answer
233 views

Update Google Drive Chart based on which row selected

I have a table with some pretty basic data (it's a time series going across the columns, and different budget items going down). I want to create a chart that plots a particular row's data against ...
1
vote
1answer
286 views

Google spreadsheets Conditional Formating Formula

I am having trouble highlighting all cells in a column that do not contain the same text that is found in the first cell at the top of the column. I would like to accomplish this with a conditional ...
1
vote
1answer
722 views

Calculate score and ranking in Google Spreadsheet

I want to create a Google Spreadsheet which will work as a ranking system for a game. One sheet, "Ranking", should display the current ranking of the players, based on their points. Another sheet, ...
1
vote
1answer
1k views

Conditional formatting with if formula

On Google Spreadsheet, I want cell D88 to change background color depending on the value in E88. In my head this should work like this (imagine it's a conditional formatting window): ...
1
vote
1answer
3k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...
1
vote
1answer
85 views

How many named ranges can a single Google Spreadsheet have?

Is there even a limit to the number of named ranges that you can create within a single Google Spreadsheet? It's rather cumbersome to add a new one, so I would prefer to not just open a spreadsheet ...
1
vote
1answer
80 views

Importing Columns of Data

I'm trying to import columns of data from different sheets. Examples SEARS -- (sheet one) (I'm entering the prices manually) 1/2013 $8 2/2013 $5 3/2013 $14 Here is sheet two: (I'm entering ...
1
vote
2answers
38 views

Find items that are not duplicates

I have these cells A1 through A7: Mary Susie Jane Mary Susie Elizabeth Mary I want to get the names that do not appear multiple times, so my results should be Jane and Elizabeth. I don not want ...
1
vote
2answers
390 views

Make a counter, that counts cell change

I have a cell, let us call it A1, that contains an IF function. Cell A1 changes between two types of text: Healthy Injured What I want is a counter in for example B1 that counts how many times A1 ...
1
vote
1answer
785 views

Google Sheets Print Filtered Rows

I use a Google-Sheet (sheet called "Data") for my payroll. In A1 I paste data from another spreadsheet that I use to store all the data for my business ( example value of job, location, date, ...
1
vote
1answer
61 views

Custom Score for Spreadsheet

I have a Google form that has multiple choice answers. I want to score the document (the spreadsheet that is generated) by giving each answer a numerical value (not just right or wrong, but answer A=1 ...
1
vote
3answers
209 views

Google Spreadsheets: How to drag down 'A1:A2', 'A1:A3', 'A1:A4'

Title basically says it all lets say every day (1 day = 1 row) a number is being added in column A I want to have column B show the total of these numbers. However, if I drag it down, it keeps ...
1
vote
1answer
1k views

How to search multiple columns in Google Spreadsheet and return values in other columns?

I have two spreadsheets and would like to enter a value into cell A1, have it check multiple columns in a spreadsheet named 'data' and output information from other columns if it's a match. ...
1
vote
1answer
60 views

Group by and ordering inside a group in Google Spreadsheets

I have the following data in a Google Spreadsheet: 1 shiplu rice 2 sharmin rice 3 sharmin fast food 4 sharmin salad 5 rafiq burger 6 nazia noodles 7 rafiq salad 8 rafiq ...
1
vote
2answers
35 views

Take data from a specific row

I'm making a form where people can enter in certain dimensional grades for a specific name. So each row of the spreadsheet will probably read something like: Name | Strength | Agility | Luck John | ...
1
vote
1answer
39 views

sharing only select rows

I'm trying to create a sheet with data compiled via application forms which are filled out by many people. I want to be able to edit that information and share portions of it with the people who fill ...
1
vote
1answer
380 views

Daily running average in Google Spreadsheets possible or not?

I work at a job wherein I receive tips. I have set up a sheet to keep track of the tips I make throughout the year. I have all the formulas set up that I want, save for one. I am trying to figure out ...
1
vote
1answer
338 views

Hide gridlines in Google Docs spreadsheets by default?

I general like to hide spreadsheet gridlines. Google Docs has them on by default when you create a new sheet. Is there any way to have them hidden by default?
1
vote
2answers
490 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
126 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
1answer
252 views

Time Triggered Script to Change Cell Contents

What I want to do is for example: A time will be manually entered into a cell say D3, when it is 20 minutes before this I want cell D1 to change to "20 MIN", WHEN IT IS 5 minutes before I want to ...
1
vote
2answers
720 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
66 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
527 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
742 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
381 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
124 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
402 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. ...
1
vote
1answer
765 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
146 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
1k views

Script error trying to populate form drop down menu from spreadsheet

We are trying to link a list on a Google Spreadsheet to a drop down menu on a Google Form. The twist is that this Google Spreadsheet list will be continuously updated and needs to automatically ...
1
vote
1answer
1k views

Insert column across multiple sheets

I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats). Is there a way to insert a column on one ...
1
vote
1answer
312 views

How to convert USD to NIS in Google Spreadsheets?

I need a function that converts American Dollars to New Israeli Shekels in a Google Spreadsheet. Is there such a function, or a viable workaround?
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 ...