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

learn more… | top users | synonyms (1)

1
vote
1answer
46 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.
1
vote
1answer
235 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 ...
1
vote
1answer
121 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
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 ...
1
vote
0answers
45 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.
1
vote
1answer
303 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 ...
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 ...
1
vote
0answers
117 views

Importing only certain rows

I am attempting to create a spreadsheet based off another spreadsheet. I only wish to import certain rows based on the second column of the first spreadsheet. If that column contains certain users ...
1
vote
1answer
218 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
0answers
147 views

setDataValidation in google script not working

Have a problem with setting data validation rules in a google spreadsheet using apps script. Below are two functions, one is an onChange function which will be triggered whenever a user changes the ...
1
vote
0answers
65 views

Google Spreadsheet protected cells

I have created my spreadsheet with protected cells scattered throughout the document in google docs. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep the ...
1
vote
0answers
50 views

Substitute for Lookup() in Google Spreadsheets

I am making a bookkeeping sheet. The aim is to create rules, that search imported fields from our bank statement, and if a match is found automatically fill the adjoining cells with the rules data. ...
1
vote
0answers
149 views

Use of importdata with arrayformula

I’m using importdata to retrieve Yahoo stock information, which works fine on indivdual rows, but when combined with arrayformula to fill down the page when additional symbols are added does not ...
1
vote
1answer
449 views

SUM of all cells in another column, except above rows

Each cell in column A should report SUM of every cell of column B, except rows above those cells. Sample: | A | B | --------- | 4 | 1 | | 3 | 0 | | 3 | 1 | | 2 | 1 | | 1 | 1 | | 0 | 0 | How can I ...
1
vote
1answer
116 views

ImportHTML and SUM the column

I am looking to use the importHTML function for loading a page and I would like to sum a particular column in Google Spreadsheets. But I am not having much success. This is the URL: ...
1
vote
0answers
168 views

Password protect a column in Google Spreadsheet

I have encountered a problem that I need a hand with. I have already done numerous searches of this forum and others to see if I could solve it that way but it appears I have come a cropper. So ...
1
vote
1answer
1k views

Google Spreadsheet how to use an if statement over several cells

Is there a function that allows me to calculate the total value of column B if column A is equal to "some text". I'm making a budget for myself. Currently, my Google Spreadsheet looks something like ...
1
vote
1answer
120 views

Get a list of items missing from array in google spreadsheet

I have a spreadsheet where I'm using the trailing set of columns as a kind of foreign key; I'm trying to throw an error if someone enters a value that doesn't exist. I'm thinking one way to do this ...
1
vote
0answers
56 views

Publish last 5 entries from a Google Form

We currently create and html table to list our last 5 Webinars. This is done manually. We are adding external contractors into the mix and would like to give them the ability to enter basic webinar ...
1
vote
0answers
58 views

Automating Google spreadsheet to transfer new info

I'm looking to setup a production line with Google Spreadsheets. Here's how I intend it to work: Our Sales Rep John has a spreadsheet specific to him only. He takes in sales leads, and inputs the ...
1
vote
0answers
399 views

Google spreadsheet: protect formatting while allowing to change data

As the title says, I would like to protect the formatting of a cell (or a range of cells) in a google spreadsheet, but still allow changing the data in that (or those) cell(s). For example, I want to ...
1
vote
1answer
932 views

Data Ranges in Custom Google Spreadsheets Function

I'm trying to create a custom Function in Google Spreadsheets that takes a range as input parameter. function Example(cells){ var values = ...
1
vote
2answers
538 views

Google Spreadsheet =ImportHTML #value error for specific URL

I'm trying to get the data from http://wodindex.wikispaces.com/ into my spreadsheets. Also from a different subdomain of wikispaces.com the grabbing doesn't work. Trying to get data from this site ...
1
vote
2answers
234 views

How can I make a Google spreadsheet Function reference one column's value and adjust it to a different Value?

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be ...
1
vote
0answers
81 views

Automatic data sharing between Google Docs files

Is it possible to automatically update information on a Google Presentation or Document based on information in a Google spreadsheet? For example, I could create a presentation that contains a graph ...
1
vote
1answer
108 views

Custom Actions in Google Drive Spreadsheet

I find that I often change specific cells from black on white to white on black when I'm making spreadsheets. It get's tedious to change the background color, then the foreground color over and over ...
1
vote
0answers
69 views

Relative paths to other sheets inside a Google Spreadsheet

I'm trying to put our food recipe collection into a Google Spreadsheet. On the first page we have an index of all the foods. Each recipe is located on a separate sheet. We would like to be able to ...
1
vote
0answers
235 views

Google Sheets: Reference one sheet from another, where the user may not have view access to the referenced sheet

This is what I'd like to do: The public (even you!) would have view access to a particular Google sheet. It would draw data from another Google sheet, which you DO NOT have any access to. Using ...
1
vote
0answers
79 views

Auto paste formula on form submission

I have searched and tried various solutions elsewhere but none seems to work for both conditions. I would like to have the formula: =IF($A2="Elementary", VLOOKUP($B2,'ELEM PRINCIPAL'!$B:$AA,7,false), ...
1
vote
1answer
130 views

Inserting rows and adding a cell reference

I'm trying to automate adding new rows if I'm on the last few rows of my sheet. It works, but since I'm also adding a cell reference to it, and checking the amount of free rows left using ...
1
vote
0answers
710 views

Google Apps Script UrlFetchApp and password protected pages

I'd like to be able to use Google Spreadsheets to login to my registart and automatically list into rows the current name server and email settings of all my sites, rather than use the heinously ...
1
vote
1answer
148 views

Creating multiple instances of a cell value in Google Spreadsheet

I have a Google Spreadsheet with the following data: A B ----------------------------- 1 | Number of responses | Value 2 | 1 1 3 | 3 ...
1
vote
0answers
414 views

Set a Category Filter in a Google Spreadsheet Line Graph

I've got a google spreadsheet that tracks status updates to a series of projects over time. The table rows each list a particular month (01/2012) and the columns list the status change ("Ready for ...
1
vote
1answer
577 views

Google Spreadsheet Form Responses in to an already made sheet

I have a template form and matching spreadsheet that need to be copied multiple times. When I make copies of the two, the copies are not linked for the form responses to go to the newly copied ...
1
vote
2answers
593 views

How to SUM() a column from one Google Spreadsheet in another spreadsheet

Has anyone been able to return the SUM() result of a column in one Google Spreadsheet to a different spreadsheet? I am trying the ImportRange(key, criteria) but I am not having any success. I am just ...
1
vote
0answers
439 views

ARRAYFORMULA Using Row Above - Google Spreadsheet

I would like to create a column that contains a running decrementing tally, but is decremented by an amount from a different column on the same spreadsheet. Also, I would like to be able to add rows, ...
1
vote
1answer
288 views

IMPORTRANGE formula query to query data in another file

Is it possible to create two different files where in the first I put the data and in the second the references query? I used IMPORTRANGE but this method gives me "=CONTINUE(A1; 2; 1)"... I want to ...
1
vote
2answers
665 views

Problem QUERY function in Google Spreadsheets formula

I tried this formula: =concatenate(query('Scheda Intervento'!C2:K14;"select SUM (K) where (F>= date '2013-01-01' and F <= date '2013-01-31' and C = '" & A2 &"')";0)) The result is ...
1
vote
0answers
260 views

How to tally entries by user, date

I am using a Google spreadsheet to track entries by several users. How do I measure their productivity per day? Or per hour? I have the timestamp field, a user field and other fields, but what I want ...
1
vote
0answers
349 views

How to protect table cells in Google Spreadsheets from myself (accidental edits)?

As in Microsoft Excel, I want to protect certain cells in Google Spreadsheet. This is possible, but asides from the fact that it is quite confusing to me, it doesn't seem possible to protect cells ...
1
vote
1answer
246 views

Count related to date items in a Google spreadsheet

I have a spreadsheet that shows how many are still completing a task at two and 8 weeks. Col A Name Col B has Start a date Col C is the WeekNumber of the date from col A (Using WeekNumber Script) ...
1
vote
0answers
497 views

Google Drive Form doesn't display on Google sites if not signed in on chrome

I have a site, hosted by google sites that includes a Spreadsheet Form from google drive. Normally, the page loads, then a second or so later the form loads. In the problem case the page loads, but ...
1
vote
3answers
133 views

Google Spreadsheets: how to show the 10 most recent rows from another sheet's timestamp?

In one sheet A I have an ongoing number of rows where the 1st column is a timestamp in ascending order (more recent entries at the bottom). I'd like to have another sheet B where only the 10 most ...
1
vote
0answers
545 views

Sorting on a web-published Google Spreadsheet

I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc. I would ...
1
vote
0answers
92 views

In a Google Sheets chart how do I prevent “Other” in the legend?

When there are over 8 series in a chart (this is true at least for a pie chart) - Sheets combines all other series above the 8th into "Other" this is really counter productive when there are only 9 ...
1
vote
0answers
601 views

Google Docs capabilities

We've created a commission calculator to help our resellers work out what commissions they will receive on a deal. We built it in Excel 2010 but want to move it to a web based platform that will ...
1
vote
0answers
547 views

How to make a query with Google Spreadsheet, that returns rows if deadline is two weeks from today, one month from today, 2 months from today, etc

I have a spreadsheet that contains several columns with project information. I basically want to modify the following formula: =QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 ) ...
1
vote
1answer
70 views

Referencing a Range Name from a different spreadsheet

I would like to create a spreadsheet that contains name, phone number, address, email address of many individuals and I will define the data as a range name "Index". Then I would like to create a ...
1
vote
0answers
219 views

Google Sheet document using ImportXML doesn't Automatically Republish

I created a file that uses ImportXML to pull prices from a set of web pages, and bless me, it works! To stay updated with the changes in price, I ticked the "Automatically republish when changes are ...