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

learn more… | top users | synonyms (2)

1
vote
1answer
2k views

Google Docs: How to Paste a Cell Range Automatically—Values Only?

I need some "live" cell data (values only), which get their data from another sheet; an arrayformula is not sufficient. I'm thinking something that copies column A:13-down from Sheet1 (whenever it ...
1
vote
1answer
434 views

Add all positive values in column

In MS Excel I can add all positive values in a column with SUMIF(A:A,">0") But this isn't working in the Google Docs Spreadsheets. The ">0" part is not parsed and gives an error. Is there ...
1
vote
1answer
82 views

How do I add a range of numbers positioned relative to its criteria?

I'd like to sum up some named values by referring to their names in a separate sheet. Can I do this with a single formula in the second sheet? Sheet 1: | A | 3 | | B | 5 | | C | 2 | | D | 4 ...
1
vote
1answer
297 views

Handling variables with data from another sheet on google drive calc

I have a Google Drive Calc File, Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the new ...
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 ...
1
vote
1answer
291 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 ...
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 ...
1
vote
1answer
295 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 ...
1
vote
2answers
432 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
784 views

SPLIT function in Google Spreadsheets with OR statement

I want to use the split function, but with different delimiters. I thought of something like this: =SPLIT(A:A;OR("-","&")) but it doesn't work. Is it possible to have Boolean statements within a ...
1
vote
1answer
120 views

Transpose an Address List as Row in Google Spreadsheet

I'm having a good old list with addresses: Because I want to re-arrange the list (and make it more 2013), I need to transpose the list like this: I've tried to use the TRANSPOSE formula, but that ...
1
vote
1answer
248 views

In Google Sheets, how can I fit the data to the cell?

In some cells on my sheet the data fits the cell. On some rows there is a lot of extra space after the data in each cell. I have tried deleting after at the end of the data, but it does not help. ...
1
vote
1answer
79 views

Formula to return time difference but not display 0 in google spreadhseet

I have a google spreadsheet with start (C) and end (D) timestamps. I am calculating the time difference using =(D1-C1). I have copied the formula to empty columns which returns a 0 time difference. Is ...
1
vote
1answer
163 views

Forwarding table content received in Gmail to a spreadsheet in Google Docs

I am managing to receive a fixed structured table from a questionnaire in a web form. So I want to have this table automatically forwarded to a specific spreadsheet, so that I can make my own ...
1
vote
1answer
706 views

Escape parentheses around a number in Google Docs Spreadsheets?

How do you escape parentheses around numbers in Google Docs Spreadsheets so that they don't default to displaying as negative numbers?
1
vote
1answer
137 views

In Google Spreadsheet, how to copy data from cell below?

Envision two rows, one up top and one below it. The cell up top is blank but the one below has 9/24/12. How can I create a function that will make the top one copy the value of the cell below it if it ...
1
vote
1answer
828 views

Make new responses appear as a row at the top of a Google Spreadsheet

I made a web form based off of a Google spreadsheet. New responses currently manifest as new rows on the bottom of the spreadsheet. There is a "Timestamp" column marking the time of submission. How ...
1
vote
1answer
190 views

How do I get the unique number of days in a datetime column in Google Spreadsheets?

I have a column full of datetime values in a Google Spreadsheet. I want to get a count of how many unique days there are. As in 8pm and 7pm today would count as 1. Tomorrow at 9pm, 3pm and 4pm would ...
1
vote
1answer
616 views

How come I can't input a formula into my Google Spreadsheet?

Here are my steps: focus a cell in my Google Spreadsheet, hit = and then highlight the field D3, hit +, highlight the field D4, hit +, highlight the field D5, hit Enter. However, the field just ...
1
vote
1answer
2k views

How to query for count of dates in Google Docs?

I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
1
vote
2answers
864 views

In Google Spreadsheets how do I select column 1's value if an input value is between the integers in colums 2 and 3 for a range?

In a Google Docs spreadsheet I have two tables: Date BMI BMI Outcome 2011-06-21 22.1 ? 2011-06-22 21.8 ? 2011-06-23 21.5 ? and Name From To Severely ...
1
vote
1answer
2k views

Validation based on multiple sheets in Google Docs

I have 2 sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet. What would the formula look like for this?
1
vote
2answers
1k views

Google Spreadsheet: Auto-Update via RSS (and keep history)

I have a google spreadsheet that fetches some data via RSS. The RSS feed is updated once a day, everyday. Is it possible for me to keep archive of all the new & historic data in the google ...
1
vote
3answers
6k views

How can I retrieve records from a Google Spreadsheet in JSON format?

I'm trying to retrieve data from Google Spreadsheets with no success. I've gone through this: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#RetrievingTables and this: ...
1
vote
1answer
22 views

How to change the size of a published Google Spreadsheet chart that has its own sheet?

Google Spreadsheet offers the option to put a chart on its own sheet (instead of inserting it in the current sheet). This looks like a good idea for large charts. However, I ran into an issue when ...
1
vote
1answer
17 views

Trouble linking entries from two sheets for automatic update

How do I have text information from one column (Sheet 1, column C) automatically feed into Sheet 2 column A so that, when Sheet one is updated through the Google form it is linked to, the information ...
1
vote
1answer
27 views

I need to hide/show a group of columns in Google Sheets in a simple way

I need to hide and show in a easy way a group of columns in Google Sheets. In Excel I used a Macro with a button, but in Sheets I cannot put a button. How can I add buttons with custom functions in ...
1
vote
1answer
17 views

Google Spreadsheets Dropdown Lists

So I'm using Spreadsheets to tabulate some data. I have 2 sheets, one that displays everything with drop down lists, and the second that has the information. The second sheet looks like this: ...
1
vote
1answer
18 views

How do I find what is in a particular cell on the same row as another on based on a match?

I have the following query in Google Sheets: =IF(ISERROR(MATCH(A2,Sheet1!A:A, 0)), "No Match", "Match on Row " & MATCH(A2,Sheet1!A:A, 0)) I want to return the value in column C of the matched ...
1
vote
1answer
15 views

Why does not the picture in column A move with the sorting A to Z of column B?

I'm using Google Spreadsheets, first column is picture then B column is name. When I want to sort it A to Z, all the names get in alphabetical order, but the picture stays in the same position.
1
vote
1answer
21 views

For range X, when empty grab column A and list them in a single cell, coma separated

I'm compiling stats for our Boom Beach team, which means I have to log attacks by each players for each operations. An operation is launched every day and all players should perform their attack in ...
1
vote
1answer
20 views

Complex Sum Function in Google Sheets

Apologies if this is lame, but I can't get a proper value using =SUM to perform order of operations on an employees hours (he has gotten two raises since starting after surpassing expectations). The ...
1
vote
1answer
26 views

Combine image and text in Google Spreadsheet

In Google Spreadsheets it is possible to show an image in a cell using the formula =image('http://www.example.com/path/to/image.ext', 3) (where 3 means to use the images' original size). I would like ...
1
vote
1answer
20 views

In a google spreadsheet, how can I delete all rows containing given text that could be in any column within the row?

I'd like to be able to delete all rows in a google spreadsheet where a given text string exists anywhere in the row. The text being searched for could be in any cell in the row, and also could be part ...
1
vote
1answer
25 views

How to add line breaks in a cell before specific keywords in Google Sheets

I'm new to Google Sheets and I was wondering how to tell GS to insert line breaks in a cell with text if there are some one or two letter before the text. For instance, I have a cell like this: I ...
1
vote
1answer
28 views

Change an input value in a cell to another value depending on what the input was

What I would like to do is change a number input in the rating column (G3:G9001) to the corresponding number of stars (from 1-10). Howerver I could not figure out how to do this. As a test I tried to ...
1
vote
1answer
24 views

Create a function to copy range (values only) to a new tab at specified intervals

Looking to find a function that can copy the values only from a range I am importing from the web at a certain time. Have found a lot of functions that are similar to what I need but can't nail one ...
1
vote
1answer
28 views

Filter rows by one unique column of sorted values in Google spreadsheets

I need an equation that filters by unique ID, using only the latest row (most recent row in time), and data found in that row. I have data that is in the following format: ID | Price | Area | ...
1
vote
1answer
43 views

Calculate difference between numbers in 2 columns row-by-row in a spreadsheet

I have 3 columns in a Google Docs spreadsheet: A) Inventory purchased price, B) Inventory sold price, C) Difference between A and B (i.e. profit or loss) Data is going to be entered manually into ...
1
vote
1answer
12 views

Searching through sheets

I need to search multiple sheets like this for certain words like #work and #hobbies and add up the numbers on the adjacent column. ...
1
vote
1answer
34 views

Decimal conversion of time values

What is or can it be done to add 2 cells, that return a time equation? I deal with multiple time entries and having to enter them in a standard format of 4:45 hours or 4:25 hours. I have a decimal ...
1
vote
1answer
36 views

Google Sheets: average cells in Row 4 up to last filled cell in Row 2

Is it possible to write a Formula in Google Sheets which calculates the average of the cells in Row 4 but only up to the last filled cell in Row 2?
1
vote
1answer
51 views

How do I use a dropdown menu in a cell to populate other cells with information?

I'm trying to build a spreadsheet that allows a user to select a product in a dropdown menu in the first cell and then based on what is selected it will populate the spreadsheet with prices and ...
1
vote
2answers
21 views

Google Sheets rounds numbers where not wanted

This: 3.1415926535 produces: 3.141592654 How can I have it output the whole number as is? I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to ...
1
vote
1answer
59 views

ImportRange with preserved formatting in Google Sheets

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. Cell reference with colour formatting This would be the perfect option, except I ...
1
vote
1answer
42 views

Copy formula from Google Sheets, paste as formula in TextEdit

How can I copy formulae (like =D3) from a range of cells (several rows and/or columns), and paste those formulae (not the values) somewhere in like TextEdit or anywhere else? Normally it will just ...
1
vote
1answer
68 views

How do I Import a Range from one Google Spreadsheet into another?

I am getting the same error as described here except not for the same reason. I am using the new Google Sheets, so I am supposed to be able to paste the entire source URL into the sheet, like so: ...
1
vote
1answer
28 views

Can you limit the average function in Google Spreadsheets?

I have a table with column A having a 0 or a 1, and column B having a value that I want to average. I know I can use averageif(A:A, "=1", B:B) to get the average of all of the values that have a 1 in ...
1
vote
2answers
33 views

Operations on hyperlink labels of cells

I am using hyperlinks on some price values on cell in Google Docs. So my cells look like this: =HYPERLINK("http://www.site.com/item","$25.00") I am trying to perform a SUM operation on a bunch of ...