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

learn more… | top users | synonyms (1)

4
votes
2answers
895 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
283 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
82 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
746 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
1answer
609 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
1answer
914 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
1answer
74 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
432 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
4k 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
1k 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
853 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 ...
1
vote
0answers
332 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, ...
6
votes
1answer
30k 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
204 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
349 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
453 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
273 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 ...
1
vote
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
227 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
2k 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
223 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
1k 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
988 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
205 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
224 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
892 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 ...
3
votes
1answer
5k 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
3answers
4k 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
637 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
343 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
154 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
283 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
2k 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
90 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 ...
1
vote
0answers
315 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
247 views

Conditional formatting for dates in Google sheets

I work at a language school and I have a column giving the students expected arrival date. I want to set conditional formatting to: Highlight in red any date that is older than 1 week Highlight in ...
1
vote
1answer
5k views

How do I automatically update a cell in Google Spreadsheets to the most recently entered in the same row?

I am trying to have one column that will show the most recent thing i entered in the row. Example: I want B2 to be the same as D2 but once I enter something in E2 I want B2 to display that. And then ...
2
votes
1answer
1k views

Google spreadsheet: named range built upon formula

I have a list of items (cells A1 to A7) that are counted in cell B2 (whose value is currently 7), B2's formula is something like =COUNTA(A1:A10000). That way if I add some value in cell A8, the count ...
1
vote
1answer
235 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
1answer
137 views

Finding and removing periods in Google Spreadsheet

I'm using a Google Spreadsheet and trying to create a formula to remove a period and anything that comes before it in a cell. For example: idontwantthis.iwantthis I have used this in the past: ...
1
vote
0answers
477 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 ...
0
votes
0answers
334 views

Google spreadsheet revision history — too much detail

For word processing, Google Docs revision history is great. However, for some of my spreadsheets, something isn't working right: Revisions are not grouped together, multiple for each minute. "Show ...
0
votes
1answer
316 views

Google Spreadsheet won't display the leading space in the cell

For following graph, the Google Spreadsheet won't display the leading space before android:xxx. How could I enable it or how could I add a leading space ? <set ...
1
vote
2answers
307 views

Google sheet: sort value according to another column

I'm a total n00b to this, so I'll try to explain the best I can. I have a Google Spreadsheet prepared: example sheet In column I, I want to list every player in column B if the value in column C is ...
1
vote
1answer
1k views

How do I disable Google Spreadsheet comment notifications?

I've got a shared document with a co-worker and they're adding comments. The system keeps sending me emails with every comment she adds and it's rather tiresome. Google Support says there should be a ...
1
vote
1answer
484 views

Copy one Google Spreadsheet to another

I am developing a Google spreadsheet that I want to make publicly accessible to a community. I've released the first version of it, and now everybody in the community has that first version's link ...