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

learn more… | top users | synonyms (2)

0
votes
1answer
142 views

Google Spreedsheet, Auto sort in 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
682 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
462 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
88 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
1k 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
152 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
660 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
354 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 ...
6
votes
1answer
2k views

Summing a column 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 ...
3
votes
1answer
660 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
329 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
2k 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
305 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
85 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 ...
0
votes
0answers
900 views

How to make Google Spreadsheets recognize data as numeric

I have the opposite of a fairly standard problem -- how do I force Google Spreadsheet to understand that a value is numeric? It seemed like using Format > Number should do the right thing, but it did ...
1
vote
2answers
753 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
116 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
558 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
3answers
6k 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
998 views

delay in updating referenced cells when using importrange in google drive

Im using the importrange() function in Google drive to reference data from 'spreadsheet1' and display in it 'spreadsheet2'. This works fine, but 'spreadsheet1' and then go to 'spreadsheet2' its not ...
0
votes
0answers
438 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, ...
7
votes
1answer
40k 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
273 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
426 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
2k 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
493 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
283 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 ...
2
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
277 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
3k 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
306 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
2answers
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
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 ...
2
votes
1answer
250 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
276 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 ...
0
votes
1answer
2k views

Google Spreadsheet: Adding formulas to Google Forms

I have a sheet with form data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already found out any ...
5
votes
1answer
8k views

Google Spreadsheet: FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal"), as "OR" results ...
5
votes
4answers
5k views

How to check if value is in range of cells?

I want to check if the value of a cell exists in a range of cells. How can I do this? Just like you would be able to do "if value in list_of_values" in Python for example.
3
votes
2answers
773 views

Google Spreadsheet: Finding the SUM of an array where the size of the array is dependent on a cell's contents

Here is a simplified example of what I want: sheet 1 sheet 2 The yellow highlighted areas are areas important to the formula. Essentially Column C finds the row on Sheet 2 with the same Item as ...
2
votes
1answer
472 views

How can I make text flow out of a cell as it does in Excel?

If you enter a long bit of text into a cell in Microsoft's Excel the text flows out of the cell over the top of the adjacent cells. This doesn't happen by default in Google Spreadsheet - it flows ...
0
votes
0answers
159 views

Create new document within spreadsheet and link

I would like to create a spreadsheet script that allows me to create a new document every time I click on a new cell or row and then have that document links back to that cell.
2
votes
1answer
402 views

Enabling script to run as author in a shared Google spreadsheet

I created a spreadsheet that I'd like to share with anyone with the URL. The sheet contains several app script functions. These functions both change the content of the sheet and copy data to ...
3
votes
3answers
3k views

Google Spreadsheets: Excluding Negative Values

Is there a way to get a function to return any result below 0 as 0 or not include negative numbers in sums? I'm using it to track my total overtime through the year at work. So right now I have it so ...
2
votes
1answer
122 views

Statically positioned chart (ignore scroll)

I have a Google Spreadsheet with 4 columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen. Is there a way to make it static ...