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

learn more… | top users | synonyms (1)

1
vote
1answer
26 views

Lookup using a key from a dropdown cell

I have a dropdown that selects from several text "keys," like so: List of items: Pathetic,Untrained,Novice,Adept,Master,Amazing I'm trying to use these keys to run a lookup on a separate sheet, with ...
1
vote
1answer
16 views

List all records with certain criteria

Help. I just cannot get this going even after 2 hours of searching Google. I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
1
vote
1answer
24 views

Counting Instances in Google Sheets

At my school, we have a Google Form for recording classroom discipline. The form generates the standard spreadsheet. I want a list of how many times each unique student has received a disciplinary ...
1
vote
1answer
50 views

Google Spreadsheets function to filter by date and count text strings in certain fields

I'm not very experienced with Google Sheets, but I found a function here on Stack Overflow and modified it a bit to do almost everything I need: =COUNT(FILTER('2014 Form Data'!$A:$A,'2014 Form ...
1
vote
1answer
50 views

Conditional Cumulative Sum using Arrayformula

I saw a nice solution for the cumulative sum problem here: Cumulative Sum without Script My question is how do you make this conditional? I mean cumulatively sum only numbers that belong to a certain ...
1
vote
2answers
52 views

How to get a Google spreadsheet cell to display a number as 4 digits, even if it's less than that

This is a formatting issue. Basically, I would like to get all the numbers I enter into a column become 4 digits in the other column ("output column"), with 0's in front as applicable. E.g. when I ...
1
vote
1answer
35 views

Google spreadsheet sort auto update

I have this table, which is a ranking, the values are updated automatically (data gathered from other files). What I want is that every time this values are updated the ranking automatically updates ...
1
vote
1answer
66 views

Have a range refer to a given row even if the row number changes?

I have a table like so: A | B ------------------ 1 || Name | Age 2 || joe | 28 3 || bob | 14 4 || Size | Weight 5 || small| 150 And I have a formula to sort each section by column B: ...
1
vote
1answer
51 views

Lookup/Copy data from different sheets?

I am creating a schedule sheet in Google Spreadsheets that will have a number of different people inputting their own information. I would like the Director to be able to open a single document that ...
1
vote
1answer
68 views

Sum of dynamic number of values derived from fomula

In short my question is this. If I use the SUM function (or some other function) that can take one or more arguments, is there some way I can pass in these arguments as an array which varies over some ...
1
vote
1answer
34 views

Freeze first row of Google spreadsheet when displayed in Google site

When a Google spreadsheet is displayed in a Google site window, the complete table being displayed moves when the displayed window is scrolled. The top row containing headers moves out of view. This ...
1
vote
1answer
86 views

Create a Google Sheets chart with multiple data ranges with separate key columns

I have this data: X1 Y1 X2 Y2 1 20 1 25 2 20 3 42 3 20 6 50 5 25 8 50 10 30 10 50 I want to create ...
1
vote
1answer
91 views

Conversion from Excel to Google Spreadsheet - sum across sheets

I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following: There are a few cells with formulas like ...
1
vote
1answer
56 views

What's wrong with this Google Apps Script code?

Forewarning: I'm a noob at JavaScript, with varying experience and less knowledge. I don't get why this code fails with TypeError: can't call method "getSheetName"/undefined. (line 7, file "Koodi"). ...
1
vote
1answer
28 views

Auto-Import Master Spreadsheet

I'm fairly new to Google Spreadsheets and completely useless when it comes to functions and formulas. Is there a way to take info from multiple sheets within a workbook and compile them into a ...
1
vote
3answers
3k views

How do I get linked Google spreadsheets to auto-update when 'I am not looking'

My question is similar to this one, for which no answer has yet been posted. How can I get Google Sheets to auto-update a reference to another sheet? I am filling one Google Spreadsheet with Zapier ...
1
vote
2answers
54 views

Is it possible to prevent people from entering data into my Google Spreadsheet?

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2. Maybe my explanation is better with an ...
1
vote
2answers
44 views

How can I prevent users from reverting to an old version of a Google Spreadsheet?

I would like to allow users to edit a Google Spreadsheet, but not be able to revert them to prior versions. Is this at all possible? USE CASE The spreadsheet we use keeps track of snacks consumed, so ...
1
vote
1answer
286 views

How do I sum all even or odd columns in Google Spreadsheets?

The following is given as a solution for Excel: =SUM(IF(MOD(COLUMN(B16:AO16),2)=0,B16:AO16,0)) =0 changes to =1 to toggle between odd and even columns. However, this doesn't work in Google ...
1
vote
2answers
94 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
85 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
552 views

How to calculate difference in hours between two time fields

I have two time-formatted cells and I need to calculate the difference (eg 10:30 PM) in hours between the two. Ie. cell 1: 10:30 AM, cell 2: 1:15 PM. Simply =B1-A1 will not do the trick.
1
vote
1answer
53 views

“Go to” search in google spreadsheet

I want to have a search feature at the top of my spreadsheet that will actually take me to a row, on the same sheet, based on searching a column for the matching information. So, I want any user to ...
1
vote
1answer
29 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
169 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
42 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
129 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
149 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
1k views

Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn't open?

We have a Google Spreadsheet that pulls data from an external source via an IMPORTDATA() function and then a Google Apps Script that is on a timed trigger that performs other things with the imported ...
1
vote
1answer
56 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
57 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
495 views

How does one filter pivot tables by date range in Google Spreadsheets?

I have a Google spreadsheet that keeps track of time spent on graphic design projects. I am trying to run pivot table reports to understand where we can optimize projects. In these pivot tables I ...
1
vote
1answer
129 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
65 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
156 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
406 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
887 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
51 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
2answers
34 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
204 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
542 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
38 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
115 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
666 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
50 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
3k views

Check if Column contains value, when found return contents of another column in that row

I'm using a Google Drive spreadsheet to organize guests I want to invite to my wedding. On one sheet I want all my guests and information relating to them (are they friend/family? Are they an ...
1
vote
1answer
25 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
35 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
122 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
157 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?