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

learn more… | top users | synonyms (2)

1
vote
1answer
174 views

Download list of contact information and check if Google Account

I'm looking for a way I could: retrieve a list of all my contacts and their email's and In order to retrieve a list of all of my contacts into a Google spreadsheet, I exported them as a Google CSV ...
1
vote
1answer
287 views

Google spreadsheet multiple auto-sorting

In a Google Spreadsheet I have different group of cells to auto-sort ascending i.e. table range a3:c16 columntosortby = 2 table range d18:f31 columntosortby = 5 table range g33:i46 columntosortby = ...
1
vote
1answer
248 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 ...
0
votes
1answer
397 views

Changing the A B C … column labels

The picture above shows what I am referring to. I'm looking for a way to change these " A, B, C, ..." labels to describe better the content below each. A would contain what date, B what length I ...
0
votes
2answers
8k views

Google Spreadsheet: how to get value of cell by row key and column key

Suppose I have a workbook with two worksheets, data and view. data is structured in "database" style, i.e. with a single header row and unique keys in the first column, e.g.: Country GDP ...
7
votes
3answers
5k views

Google Spreadsheets: how to specify entire sheet as range?

The best workaround I have found so far is: worksheet_name!$A$1:$YY but ideally I would like to be able to simply write, e.g.: worksheet_name! So, does anyone know: is there a syntax for ...
1
vote
1answer
302 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 ...
4
votes
2answers
1k views

Google Spreadsheets refuses to leave out commas

On the spreadsheet in question there is a column that contains plain numbers in a particular sequence. As an example: 3040513, 3050513, 3060513 etc. Sometimes when typing in the next sequenced number ...
2
votes
1answer
8k views

Google spreadsheet: Importing only certain rows

Regarding Google spreadsheets, document 1 has many rows of data. How do I import into document 2 only those rows that have a certain value in column C?
1
vote
2answers
163 views

Auto sort a sheet so that entries remain in their categories

So I want to sort my Spreadsheet on Google but it remain in the Categories that it's laid out. I can get to it sort double columns but the categories get messed up as they move away from where they ...
1
vote
2answers
945 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
496 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, ...
0
votes
1answer
102 views

autofill the 'Average' function in Google Spreadsheets

I have a column of data. Each cell in the column represents a new day. So the 1st cell is for the 1st day and the Nth cell is for the Nth day. I want to add a 2nd column where the Nth cell in that ...
0
votes
1answer
2k views

Google Spreadsheets mixing up x- and y-axis on line chart, no option to change it

For some reason Google Spreadsheet insists on using the data I wish to have for my x-axis as my y-axis. Here I'll show what happens when I try to use my data to form a chart, and what happens when I ...
2
votes
1answer
164 views

Compute average over month when measurement is irregular

I want to measure my power consumption. So once in a month I note my meter reading. So I got the following data: 2012-12-01| 893.8| | |31 2013-01-05| 977.2|34|2.45|31 ...
-1
votes
1answer
837 views

How to get the Elapsed time of two cells hh:mm in Google Spreadsheet

In columns K and L I have time stamps. Let's say 03:30 is in column K and 04:30 is in column L. I want the total elapsed amount of time in hh:mm in column P. I have tried the average function, ...
1
vote
1answer
411 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 ...
8
votes
1answer
4k views

Summing a column, filtered based on another column in Google Spreadsheet

I have the following data in a Google Spreadsheet: +------+---------+ | Time | Include | +------+---------+ | 0:30 | No | | 1:00 | Yes | | 0:30 | Yes | | 0:30 | No | | 0:30 | Yes ...
4
votes
1answer
885 views

Looping through the range of cells to test the dates

I am trying to create a function to check the dates in 'Dt' sheet and return Match or Not Match. It works fine with when I display my answer in MsgBox, but when I try to do it in one of the ...
3
votes
1answer
369 views

How to report which checkboxes were left unchecked on a Google Forms submission?

I have a Google form that stores its responses in a Google spreadsheet. It's set up so that whenever someone submits the form, I receive an email report of the responses. There are many places in the ...
5
votes
2answers
3k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week, e.g highlight the Saturdays and Sundays in a column ...
4
votes
2answers
1k views

How to sort a sheet in Google Spreadsheet by multiple columns?

Having a table with data like: - + + - - - - - + + - - where + and - are created via '-, '+ commands, How to sort it recursively (probably other word shall be used) meaning sort ...
3
votes
2answers
3k views

Conditional formatting based on cell values in same column

In Google Spreadsheet, how do I change a cell's background color to red if the date in the cell matches the date in another cell in the same column? Column E in my spreadsheet is a list of due dates ...
2
votes
1answer
337 views

How to do a date formula in Google Spreadsheets

I have to do a query that increments automatically the date year by year. For example I use this formula: =INDEX(query('Scheda Intervento'!C3:K14;"select sum (K) where (F >= date '2013-01-01' ...
1
vote
1answer
86 views

Stop content from making cell two lines

In Excel if you have a long piece of text like a URL in a cell you can stop the cell from becoming a double-line cell and messing up the rest of your layout. It does this by just clipping the info ...
4
votes
1answer
379 views

Limitation of participants Google docs

I would like to create a registration form for a serie of lectures participants could sign up for. However, depending on the lectures, there is a different limitation of numbers of participants that ...
2
votes
2answers
890 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 ...
3
votes
2answers
1k views

Iterated variable (i) for argument on range in Google Scripts API

for (i=0; i<numItems; i++) { var cell = sheet.getRange(12,i); cell.setFormula(i); } This simple formula should go to every place on the column 12 where there is data, and replace it ...
1
vote
2answers
165 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 ...
2
votes
1answer
651 views

“Yes” if a value partially matches in a range of values, blank otherwise

I have a column of values (column A for this example) like such: Phytophora sojae Phytophora ramorum Cryptococcus neoformans Coccidioides posadasii And I'd like to create a new formula column (B) ...
2
votes
4answers
7k views

Conditional formatting color gradient

A few friends and myself have created a Google Spreadsheet where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a running ...
4
votes
1answer
2k views

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting? However, my use case requires the sorting ...
2
votes
0answers
1k views

Delay in updating referenced cells when using importrange in Google Sheets

I'm using the importrange() function in Google Sheets to reference data from 'spreadsheet1' and display in it 'spreadsheet2'. This works fine, but if I edit 'spreadsheet1' and then go to ...
0
votes
0answers
530 views

Copying cells in Google Spreadsheets and pasting into text file adds quotes around each cell

Copying cells in Google Spreadsheets and pasting into text file (tried with XCode, Notes, Text Edit - the below applies to all) adds quotes around each cell. How can I avoid those extra quotes? Also, ...
10
votes
1answer
53k views

Reference data from another 'spreadsheet' in google docs / drive [duplicate]

Is there a way i can reference data from another google spreadsheet in google docs / drive ? ie. spreadsheet-A, sheet 1, column A is a dynamic reference of spreadsheet-B, sheet 1, column E Im not ...
3
votes
2answers
353 views

How to count entries based on timestamp

I'm trying to count how many entries with a particular timestamp or range are in my table and although it’s pretty simple, I cannot find the proper way to do that. Example data: A ...
2
votes
1answer
549 views

How do I reference multiple cells in one sheet in one cell in another sheet?

I am using Google Sheets and I have two sheets in one document (SHEET1 and SHEET2). I want to be able to use a formula that allows me to: If cell A1, B1, D1 and F1 in SHEET1 have an s in them Then ...
0
votes
1answer
3k views

How to apply formula to WHOLE column and automatically add this formula to new created rows in this column?

I need some kind of "autoincrement field" in my spreadsheet. I've added =ROW() formula to first column. But as I'm adding new rows - I have to apply this formula to new created rows as well. Is there ...
0
votes
2answers
2k views

Google Spreadsheets formula question: do not include cell in a formula if blank

I am a music teacher and I use the following formula to calculate my students performance grades: =100-SUM(F3-5)*-7 How can I change the formula so it only calculates a grade if there is data in ...
1
vote
2answers
528 views

display google docs spreadsheet in mobile mode in desktop browser

When viewing a google docs spreadsheet in a mobile browser you have handy filter buttons at the top. How do I display mobile version of the page in a desktop browser? Preferrably Firefox. Edit: I seek ...
0
votes
0answers
295 views

Query google spreadsheet and display single column?

I am working on using Google Spreadsheets as a gradebook. I am attempting to display only a student's grade, not an entire classes. I am integrating this into my wordpress site. The function I want to ...
3
votes
1answer
2k views

How do I Sum Thoughout Different Sheets

How do I create a reference to a defined interval in several sheets? Something like this: =SUM(Sheet1:Sheet2!A1)
1
vote
1answer
317 views

The column from which to return vlookup responses depends upon the value of a cell

With Cell F1 a drop down menu, I'm trying to look up the value of A2 on sheet 2 and if F1 contains: "Test1 1/2", then return the data in the 3rd column "Test1 2/1", then return the data in the 4th ...
3
votes
1answer
4k views

Second highest value

I know how to use MAX(A1:A30) to get the highest value in the cell range A1 to A30. But how do I get the second or third highest value?
1
vote
0answers
362 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
3answers
2k 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
2k 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 ...
2
votes
1answer
318 views

Repeat range under each other in Google Spreadsheets

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this? So for example I have this range: Year | ...
1
vote
1answer
328 views

How do I write a simple loop in a Google Spreadsheet?

I'm trying to write a simple formula that concatenates each entry in a given column of a spreadsheet, interspersed with newline characters. In other words, =CONCATENATE(B2, CHAR(10), B3, CHAR(10), ...
1
vote
1answer
1k views

Google Spreadsheet: Query function date formatting not working

I'm trying to make this query work on my spreadsheet on chrome Win7: =QUERY(A1:D5;"SELECT MAX(A), COUNT(A), SUM(C) GROUP BY (A) FORMAT MAX(A) 'MMMM' ") Everything is ok except for ...