One of productivity apps in Google Drive, that allows you to create and edit worksheets
1
vote
0answers
184 views
How to make a query with Google Spreadsheet, that returns rows if deadline is two weeks from today, one month from today, 2 months from today, etc
I have a spreadsheet that contains several columns with project information. I basically want to modify the following formula:
=QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 )
...
2
votes
2answers
249 views
Google Spreadsheets: Counting minutes/seconds
I am using format-value-time to have cells where I input minutes/seconds, like 13:45 for 13 minutes & 45 seconds. If I have several of these cells, how can I add them all up?
Example:
Cell1:
...
2
votes
1answer
219 views
Auto date insert when opposite cell is populated
I am creating a spreadsheet which will contain records of my daily expenditure. Something like the following:
A | B
----------
0 | 50.04
0 | 20.00
0 | 19.99
0 | 2.40
What I am trying to ...
3
votes
2answers
235 views
How can I make some data Autosort in a spreadsheet on edit?
I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B).
I have used the following ...
1
vote
1answer
276 views
Get rid of row in result of Google Spreadsheets query
I use the following formula in my Google Spreadsheet:
QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'")
This returns the following (On two different rows that ...
0
votes
0answers
20 views
Function Question
I am entering data into the spreadsheet and I could use some type of function that would link separate columns if there is a #1 entered. Such as if a 1 is entered into column F + column K + N, column ...
0
votes
0answers
140 views
Get Published Google Doc in JSON
I have a Published Google DOc at
https://docs.google.com/spreadsheet/pub?key=0AtjON-ccqVTgdDhKeGx0UlZpUWUyLTFZV2szQlJyNnc&output=html
I want to retrieve in JSON. I've been trying this but its ...
3
votes
1answer
174 views
How can we view the whole spreadsheet instead of just the form in Google forms?
I have been given a link to a Google docs form where I was supposed to enter some data. The URI looks something like this:
https://docs.google.com/spreadsheet/formResponse?formkey=foo
Is there ...
1
vote
1answer
841 views
Google Docs Spreadsheet - Parse Error
Not sure why this formula does not work. I just want the value to be zero if 60-D15 is negative
=MAX(60-D15,0)
also tried
=MAX(D15,0)
=MAXA(D15,0)
Any clues?
2
votes
0answers
191 views
Cumulative Sum without Script
For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
0
votes
1answer
52 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 ...
2
votes
1answer
174 views
Styling Text Within a Cell in Google Spreadsheet
In Excel it is possible to style text within a cell, by pressing Crtl1.
You can create for example the following sequence: 1,2,3,4.
This that possible in Google Spreadsheet?
1
vote
1answer
94 views
Filter function
I am using filter formula below. The formula works fine the first time, but once it filters the result properly, it corrupts the same formula in next row below with --.
...
3
votes
1answer
450 views
Count Occurrences of Words
I'm having the following set of data:
A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4.
The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x)
...
1
vote
1answer
136 views
Selecting a sequence of sheets in Google Docs / Spreadsheets
I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically.
I would like to summarize some information from all sheets into ...
-1
votes
1answer
52 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. ...
0
votes
3answers
189 views
In Google Spreadsheets, how can I convert a number according to a range?
I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table.
if score is (0-5) result should be 1
if score ...
2
votes
2answers
330 views
Google spreadsheets editing of a VLOOKUP formula that references a pivot table
After inserting a vlookup formula in a Google spreadsheet, where the array parameter references acolumns of a pivot table, how do I edit the formula again?
When I enter the formula, it just displays ...
0
votes
1answer
488 views
How do you add a custom font size in Google Spreadsheets?
How do I add a custom font size for a Google Spreadsheet I have? There are some defaults like 14pt, 18pt, 24pt and 36pt, but I would like to have a font size like 30pt, is it possible?
1
vote
1answer
96 views
Combining Stuff in Google Spreadsheet
I want to accomplish the following:
string = TEST; cell reference = B2, value = ING; RESULT = TESTING
Which of the following options are the best to use:
CONCAT: =CONCAT("TEST",B2)
& ...
2
votes
1answer
1k views
Google Docs - Auto updating column showing last modify date
Trying to figure out a way to have column two auto update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing ...
1
vote
0answers
37 views
Referencing a Range Name from a different spreadsheet
I would like to create a spreadsheet that contains name, phone number, address, email address of many individuals and I will define the data as a range name "Index". Then I would like to create a ...
1
vote
1answer
351 views
How to apply a function to all the rows preceeding the current one in an arrayformula?
So I have the following situation:
In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
1
vote
1answer
61 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 ...
5
votes
2answers
1k views
Google Spreadsheet Comments
I have a Google Spreadsheet with several boxes with the little orange triangle indicating a comment associated with that cell. Unfortunately, I haven't made any comments, so cannot delete these ...
0
votes
2answers
188 views
Selecting Values from separate sheets and aggregating the results with google spreadsheet
I would like to use the query function to select values from two separate sheets add them together and do a less then comparison to another value. I have used named ranges for the data that I am ...
3
votes
1answer
160 views
Import CSV, show formatted hyperlinks, =Hyperlink(..,..)
Based on a csv import, I would like to show formatted hyperlinks. There is a formula =HYPERLINK( "www.google.com" ; "Search Google" ) that does this, but is there any way to include this in a csv ...
4
votes
1answer
111 views
Save data from Google Spreadsheet history
I used a Google form and inadvertently deleted a few rows.
I need to use the rows again. I see them with history but I can't save them and I can't print them.
How can I restore the data?
4
votes
1answer
98 views
Non-deterministic parameters to custom function
I have a problem with Google Spreadsheets: I create a custom function using the Script Editor and it only runs OK if the parameters I pass are known(constant) values.
If the parameters are ...
1
vote
2answers
207 views
Is is possible to have part of a google doc hidden publicly but able to be edited and “pushed” to LIVE?
So I have a public schedule for my business, but need to edit (which sometimes takes a few days) and then make "public"...
Can I do that?
I know I can "hide" but when I reopen, it's live and everyone ...
6
votes
2answers
405 views
Analyse frequencies of date ranges in Google Spreadsheet
I have a Google Spreadsheet where I would like to compute occurrences of date ranges. As you can see in my sheet, there is a column date_utc+1 which contains almost random date data.
What I would ...
2
votes
1answer
671 views
Compute number of occurrences in a column of a spreadsheet
I have a Google Spreadsheet with a single column that holds string values (Twitter screen names) such as "user1", "user1", "UserX", and I would like to count those values so that I can easily craft a ...
2
votes
1answer
192 views
Convert a date in Google Spreadsheet?
I have a Google Spreadsheet with a date row in UTC time, such as 12/6/2012 11:14:32
I want GMT+2 local time, so how can I convert the date in Google Drive?
And, if not possible, is there a way to do ...
1
vote
2answers
115 views
Counting unique rows in spreadsheets
How do you run the COUNTUNIQUE function on several columns?
Running COUNTUNIQUE(A1:C3) gives me 8, because there are 8 unique values, I want it to return 3 because I have 3 unique rows.
| A | B | ...
1
vote
2answers
404 views
Spreadsheets: Is there any cloud-based spreadsheet that can give user-based access to edit a column?
We're looking at a way to share inventory information across multiple users: sales, customer service, production, etc. Is there a way to lock the 'Price' column so that it can be edited only specific ...
0
votes
2answers
373 views
Sum until empty line function in Google Spreadsheet
I'm using this data layout a lot:
A B C
1 Name1 Count Total
2 A 1 =SUM(B2:B4)
3 B 2
4 C 1
5
6 Name2 Count Total
7 D 1 =SUM(B7:B8)
8 E 2
It is ...
3
votes
1answer
164 views
Google Spreadsheet text cell formatting question
I'm trying to create some sort of automated formatting for certain columns (except headers of course.) to take a string of numbers (and previous formatting; which should be stripped.) and format it as ...
2
votes
1answer
147 views
In Google Docs, how to make a column with several numbers in each cell sortable?
I am making an expenses spreadsheet in Google Docs and I need to list the numbers of the attachments for each entry. Sometimes this is just one number, sometimes it is several.
I can make this ...
2
votes
1answer
215 views
Translating a document in Google Drive
I'm trying to translate a spreadsheet I've uploaded to Drive and converted to a Sheet. However there is no "Translate" option for me in the Tools menu, as per this article: ...
1
vote
1answer
90 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
0answers
99 views
Google Sheet document using ImportXML doesn't Automatically Republish
I created a file that uses ImportXML to pull prices from a set of web pages, and bless me, it works! To stay updated with the changes in price, I ticked the "Automatically republish when changes are ...
-2
votes
2answers
150 views
Google spreadsheet questions
Can anybody tell a me how I can load a large vertical list into the margin of a spreadsheet?
e.g, I want to copy a list of stocks from notepad (aligned vertically) to Google spreadsheet with each ...
3
votes
1answer
320 views
Creating a function that runs when a Google spreadsheet is opened
I want to create a function which runs automatically when the spreadsheet is opened. This function will make the selected cell the last cell without text in a certain column. Basically, I don't want ...
1
vote
0answers
33 views
How to build a search as you type google spreadsheet
I want to build a embeded google spreadsheet that will be searchable by the way of SAYT so that not all information, but only related rows will be displayed. How can I do it?
Thank You
1
vote
1answer
428 views
Google spreadsheet sorting messes up cell comments
I'm running into this weird problem with Google spreadsheets: if the cells have comments on them and I sort the spreadsheet, the rows sort but the comments stay in the same position, and don't move ...
1
vote
0answers
114 views
How to Create a Dynamic SubTables Based On Data From Another Sheet? (Google Spreadsheet)
I'm wondering if there is a way to create a sheet with dynamic subtables based on data from another sheet in google spreadsheets?
Each student will use a form to enter data to sheet: "Entries" like ...
1
vote
2answers
1k views
How to group data in a Google Spreadsheet?
I have the following table in a Google Spreadsheet:
Name | Value
A | 10
B | 100
A | 20
B | 200
C | 1000
How can I create another table based on a query:
select name, sum(value), ...
1
vote
0answers
50 views
Filtering Form Feedback using Google Docs
Does anyone know how to filter respondent feedback into different sheets on the spreadsheet? For example, each sheet collects information for separate classes and I want the form information to be ...
2
votes
1answer
2k views
How to sumif with multiple criteria
My sheet looks like this:
1|A | B
2|amount | source
3|173.00 | bank1
4|173.00 | cc
5|173.00 | cash
6|173.00 | bank2
7|173.00 | cc
8|SUM
This is simplified but what I want to do is Sumif A3:A7 ...
1
vote
0answers
65 views
Stop lines in GDocs chart overlapping/obscuring other lines
How can I stop lines in GDocs chart overlapping/obscuring other lines? See for example the green line in the graph below, which starts up high, butthen goes behind other lines such as the yellow one.
...
