A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
1answer
34 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
44 views

How to count specific items in column?

I just started using Google Spreadsheets. I have a question concerning "counting" a specific word/item in cells. Example: Column A 1. HAT 2. HAT 3. SHOE 4. CANE 5. SHOE 6. HAT 7. - 8. - ...
1
vote
1answer
133 views

Delete second row in a cell in Google Spreadsheet

In order to create some list, I've been copy some data from the web. Unfortunately it looks like it has been added second row for each record it need. For example: Each record is in the same ...
1
vote
2answers
64 views

Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...
1
vote
1answer
67 views

Google sheets: conditionally change sign of decimal value in cell for each row

Here's a preview of the spreadsheet I'm editing in Google Drive: The transaction type cell has a value of "debit" if the row is for an expense, and "credit" if it is income. I would like a script ...
1
vote
2answers
404 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
38 views

Checking for data duplication without direct access

I have set up a spreadsheet on Google Drive that multiple users can access and edit, but I also need users to be alerted when the data they enter already exists elsewhere (This could be another sheet, ...
1
vote
2answers
139 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 ...
1
vote
1answer
95 views

Select row in Google Sheets

I have a function in Google Sheets that is giving the following output: a b c 1 2 3 I don't want the a b c I just want the 1 2 3. What function can I wrap around my other function so that it will ...
1
vote
1answer
619 views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
1
vote
1answer
130 views

Google Sheets IMPORTRANGE + QUERY won't import IMAGE

I use Sheets in my business to create product catalogs for wholesale prices. I have different sheets for different companies but the data in many of them is the same. I'd like these sheets to sync ...
1
vote
2answers
347 views

Google Spreadsheet criteria if cell above is blank

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the column above. Consider the following example: A | B | C --------------------------------- ...
1
vote
2answers
96 views

Google Spreadsheets: Using conditional formatting from date in another cell

I have two columns side by side containing dates. The dates in column A are based on when a project is due. The dates in column B are based on when a project is completed. What I want to do is show ...
1
vote
1answer
298 views

Google spreadsheet - QUERY changes time format

I have this column with amounts of time (format set to number > duration), if I made a query of that column I get it with a different time format and I'm not able to set the proper format back again. ...
1
vote
1answer
2k views

Google sheets VLOOKUP issues not finding a value thats is in its range

Heres a link to a copy of my spreadsheet I am attempting to do the same formula using a VLOOKUP in 4 sheets to do a calculation called LSI. In the first sheet I have the raw data from a form coming ...
1
vote
1answer
69 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
1
vote
1answer
118 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
1
vote
1answer
751 views

How to pass multidimensional array to a custom function in Spreadsheets

I want to pass a multidimensional array to a custom function. I do it like this: =customFunction({{"a1v1"; "a1v2"};{"a2v1"; "a2v2"; "a2v3"};{"a3v1"}}) However I don't get the expected behaviour ...
1
vote
1answer
118 views

Function to retrieve a subset of rows coming from another function in Google Sheets

I have a function in a Google Sheet that returns a handful of columns and two rows. One of those rows contains column headings, which I would like to remove. Is there any way to wrap this function ...
1
vote
1answer
451 views

Get YouTube video's view count in Google Drive/Sheets?

Is there anyway to write up a Spreadsheet in Google Drive such that a cell will have the current (or as close to current as possible) number of views that video has received?
1
vote
1answer
153 views

Circular Dependency Error, Blank when 0

I have tried both of these in Google Spreadsheets and continue to get circular dependency errors. I am trying to show blank when the result is 0. New values are entered daily and want to have the ...
1
vote
2answers
1k views

Issue with Custom number format in Google Spreadsheet

I have a number cell formatted in this way: #,#" days";. Nevertheless, if I write 1.5the output is 1 days even if the content is still 1.5. Otherwise, if I write 1,5 the output will be as it is ...
1
vote
1answer
86 views

See last modification in Google Drive/Spreadsheets

In my Google Drive I see when a spreadsheet was modified: How to go about seeing what the modification was?
1
vote
2answers
69 views

Google sheets: is CHOOSE different from Excel?

I need to select randomly between two strings, say "A" and "B". I do =CHOOSE(RANDBETWEEN(1,2), "one", "two") It doesn't work. Neither =CHOOSE(1, "one", "two") It shows ERROR.
1
vote
3answers
123 views

Google sheet, lookup a value to get whole column

Here is my problem: I have a table starting in A1 with D D C B A 1 2 3 4 5 6 7 8 I want a function that returns me the column of data of whatever letter give it. ie input:=function("C") output: ...
1
vote
1answer
99 views

Google spreadsheet sort auto update

I have this table, which is a ranking, the values are updated automatically (data gathered from other files). What I want is that every time this values are updated the ranking automatically updates ...
1
vote
3answers
696 views

Google Spreadsheet changing cell references on its own

I have created a Google Form with an attached spreadsheet. Within that spreadsheet I have created several sheets to sort the answers to my liking. Sheet 2 is just an exact copy of all the original ...
1
vote
3answers
850 views

How to handle Negative Time Delta in Google Spreadsheets

If I have two cells, one is the starting time and one is the ending time. How do I calculate the time difference if the end time is in the next day? = B1 - A1 gives a negative time. =IF(B1-A1, ...
1
vote
1answer
590 views

Formatting in Google Spreadsheets

I am trying to format in Google Spreadsheets and am unsure how to do the following: If column G is equal to “Yes” then it will format the entire row by changing to font to grey, italic and strike ...
1
vote
2answers
140 views

Gmail message details stored to spreadsheet

I would like to know if there is a way that I could have specific messages in my email to go directly in a Google spreadsheet. For example I need all email messages with subject line of "X" be stored ...
1
vote
2answers
44 views

Adding a title to cell number 0?

It is good convention to name your columns, to put some kind of description in cell 1. However, this really bugs me, because then all my values are indexed starting at 2, so I think I'm dealing with ...
1
vote
2answers
252 views

How can I select a sheet with a variable-supplied suffix in a script?

Here is the code I'm attempting: var day=new Date().getDay()-1; var month=new Date().getMonth(); var sheet=ss.getSheetByName((/^.\s/+month+'/'+day)); I'm new to regex, but essentially I'm writing a ...
1
vote
3answers
10k views

How do I get linked Google spreadsheets to auto-update when 'I am not looking' [duplicate]

My question is similar to this one, for which no answer has yet been posted. How can I get Google Sheets to auto-update a reference to another sheet? I am filling one Google Spreadsheet with Zapier ...
1
vote
1answer
77 views

Set Selection throughout all sheets

I have a spreadsheet with multiple sheets. I want them all to scroll in sync. For example if I scroll to J18 in Sheet1, all others sheets will have the cursor at J18 and same row/column at the top ...
1
vote
1answer
941 views

Error with exact date 21/02/1971 - function MINUS expects number values

I am getting this really bizarre error. In cell A1 I have 21/02/1971 (Brazilian-style date), in A2 I have =today()-A1. I receive the following error: #VALUE! Error: Function MINUS parameter 2 ...
1
vote
1answer
71 views

How do I refer to a range in another sheet?

Sheet 1 has numeric values in the B column. I'd like to sum them into a single value within Sheet 2, preferably without having to copy them individually first. According to the docs, I can copy a ...
1
vote
1answer
547 views

Date format in Google Spreadsheet is not correct

Whenever entering the date e.g. day-month-year, it automatically changes to month-day-year. I went into format>number>more formats>more date and time formats, and I changed it to day-month-year. And ...
1
vote
1answer
98 views

“Go to” search in google spreadsheet

I want to have a search feature at the top of my spreadsheet that will actually take me to a row, on the same sheet, based on searching a column for the matching information. So, I want any user to ...
1
vote
2answers
39 views

Auto bracketed pricing in Google Spreadsheets

I am keeping track of some sales in a Google Spreadsheet. I have a price bracket for number of items sold and unit cost (so that people ordering in bulk have lower prices). I used to just have about ...
1
vote
1answer
253 views

Fix Value of Row or Column from Another Sheet

In a given sheet, if you have a formula that involves a value, it's possible to fix either the column or row for that value as you expand the equation, i.e. : =A1 + A!2 But how do you do this when ...
1
vote
1answer
2k views

How do I multiply column values?

I simply want to multiply column h with column j, and store the result in column k in each row. What do I type in as the formula to do this?
1
vote
1answer
392 views

Sum if date is in range

I have a table of random dates A and a sum for each date B. The sum is over all the amounts in D, where the date in C is before the date of A. So B3 is D1+D2+D3+D4+D5+D6 because the date in A3 is ...
1
vote
2answers
427 views

Create chart with total for each score

I have a column within a Google Spreadsheet with values like this: 96 97 96 100 97 97 100 97 95 95 96 I'd like to add a bar chart where each bar represents one of the values and the height of the ...
1
vote
2answers
52 views

Google Sheets, Suddenly decimalised some of my number entries

I've been using Google Sheets to create a record of car parts, entering: each part's serial number, the quantity of that particular part, the name of the part and the location where I'm ...
1
vote
2answers
102 views

How to display a character when a value in the same row is the highest value in its column?

I have the following example table: A | B a | 5 b | 3 c | 1 d | 9 <=== expected result e | 0 f | 4 So it should display d as in the other column 9 is the highest value.
1
vote
2answers
2k views

Having an issue with horizontal scrolling in Google Spreadsheets

When I try to scroll horizontally in Google Spreadsheets using the track pad, nothing happens. However, if I scroll vertically first, I can then scroll horizontally if I don't pick up my fingers from ...
1
vote
1answer
1k views

Is it possible to feed multiple form drop down lists?

I am using Tom Horwood's code which has worked for me for updating one drop-down. But my form has multiple drop-down lists which I would like to make dynamic. Is this possible? Tom Horwood's code ...
1
vote
2answers
198 views

VLOOKUP or INDEX+MATCH

I have an array of values: A B C 1 TypeID MaterialTypeID Quantity 2 18 34 256 3 18 35 512 4 20 34 386 5 20 36 773 ...
1
vote
1answer
123 views

Is there a way to count how many entries in a given column have a particular value?

I have a spreadsheet which keeps track of duels between two players in a game. Each row has the number of the match and the winner, given by their initial, X or Y. I want to know if it's possible to ...
1
vote
1answer
956 views

Google Spreadsheet: Count values from last 30 days

Here is a sample spreadsheet you can view and edit: https://docs.google.com/spreadsheet/ccc?key=0AnWwBCPp8kISdGc4bExyYV8xOTVRbnRkaXNQSzBWalE&usp=sharing. How can I count the number of successes ...