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

learn more… | top users | synonyms (2)

2
votes
1answer
53 views

Sum columns based on conditions

I am working for a webhosting company and we are tracking our efforts with Google Sheets. I was trying to find a solution for the following problem: We track our efforts by clients per day, so that ...
3
votes
1answer
42 views

Conditional formatting a range based on the value of a single cell inside of it

I'm trying to format a range based on the value of a single cell inside. So if I have a range like in this image: I am coloring based on the teams. If I was to rearrange the teams, how can I use ...
3
votes
1answer
35 views

How do I use DCOUNTA and get the month from a column instead of the entire date in the criteria?

I'm trying to use DCOUNTA() to count a range, the problem is that within the range there are date and times and I only want to match the months. This function doesn't seem to work: DCOUNTA('QA ...
3
votes
1answer
45 views

Time duration, when converted to decimal, shows negative

I'm trying to compute the difference of two different times to get the following: Duration of each activity (=C8-B8), then (=E8-D8) and so forth; result will show in C3 and E3 and so forth; Sum of ...
0
votes
0answers
17 views

Using Countifs to count the number of events happening some time ago

We run a series of courses. Each applicant has a date they started the course - eg 07/05/15 (UK format). What I'd like to do is countifs the number of applicants who started on a course more than x ...
3
votes
0answers
53 views

Locking cell positions to another cell if one column is using ImportRange in Google Drive

Let's say I have a master list of all kinds legos and their cost. Only two columns. Column A is the lego type and column B is the cost for that lego. Example, "3x3 thin block" and "1". or "small ...
4
votes
1answer
55 views

Filter a range by both Row and Column criteria in Google Spreadsheets

I have a set of data that I want to filter by both the rows and columns. I want to create a function like FILTER(B4:L8, (B2:L2 = "Email")+(B2:L2 = "All"), A4:A8 = "Email") to filter the data in the ...
1
vote
0answers
24 views

How to not count blanks when using the COUNTA function

I am using the following formula to count the number of time a song name is used in a column: =COUNTA((IFERROR(FILTER('Form Responses 3'!E2:E, SEARCH(C2 , 'Form Responses 3'!E2:E ))))) It is ...
2
votes
2answers
40 views

Suppress time part of the datetime values on a chart axis

I have a line chart based on the following data, capturing the Close Queue size every four hours: +---+-------------------+-------------+ | | A | B | ...
1
vote
0answers
11 views

How do I return the highest value in a Google Finance array? [duplicate]

I am trying to return the highest closing price for a stock across a specific set of dates. Finding the list of values is easy: =GOOGLEFINANCE(symbol, "price", DATE(2015, 8, 24), TODAY()) I want to ...
2
votes
0answers
46 views

How to set Audio alert in Google spreadsheet that triggers by a specific value?

Can I set an Audio alert in a Google spreadsheet cell that triggers whenever it changes a specific value? I want some thing similar to "beepnow" function which is using in MS Excel VBA.
2
votes
0answers
46 views

Create button “Add” to add a new row in sheets

I'd like to know how to add a new row copying all formulas from previous row .. What I'm looking for is as follows SR. NO ITEM CODE DESCRIPTION UNIT COST ...
1
vote
1answer
23 views

How to turn a month string into a month int in Google Spreadhseets

I am trying to take the name of a month, such as October and turn that into the corresponding number for that month 10. Past making a long formula that checks for each month by string, and without ...
1
vote
1answer
32 views

How do I conditionally format a cell based on a date within a string of text?

Suppose today is 13 October 2015. I want to apply conditional formatting rules to four cells in Google Sheets: Cell 1: 10/22 Remove cat from bookcase Cell 2: 10/23 Put books back Cell 3: ...
3
votes
1answer
82 views

Hide/Show Multiple Groups of Columns in Google Sheets

I found a script (on this post: I need to hide/show a group of columns in Google Sheets in a simple way ) that adds a dropdown menu with two options: Show Range X of Columns & Hide Range X of ...
2
votes
1answer
47 views

If Cell text is “Text” get adjacent cell text

I have a need to compare a range of data against a string of data. If there is a match, I would like to get the text or contents of a cell next to it. Any ideas? Example: Identify name of person with ...
1
vote
1answer
37 views

Change a working function from onOpen() to onEdit()

I currently have a working function that uses the onOpen() trigger to run. I am needing to make this an on edit instead of onOpen function so that a when a change is made in my master sheet, the child ...
4
votes
1answer
71 views

Splitting Cell information in Google Sheets

I'm bringing in data from WooCommerce into a Google Sheet via Zapier. It's incredibly helpful for most of the data, but there are options I need that come in as 'line items' that are not very user ...
3
votes
1answer
27 views

Using regexreplace to surround numbers by parentheses

The data I'm starting with: 1 Hello 2 Just another 22 line 300 Yeah, the first step is to admit you need help I want the "finished" version to look like: (1) Hello (2) Just another ...
2
votes
1answer
46 views

Rearranging Google form data in a new sheet

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. The thought, being any of the staff could add jobs that need to be taken, by ...
2
votes
1answer
29 views

Conditional formatting based on portion of text

Is there a better way to specify conditional formatting based on another cell? For example, I have specified a Custom formula as the following (which works): =or($A:$A="bar foo", $A:$A="baz foo") ...
5
votes
1answer
162 views

Google spreadsheet query language, match using regex?

I have a query formula that looks into a table and pulls names for example: A B 1 john, andrew blah 2 drew, mike blah I want it to show me all of the rows that ...
2
votes
1answer
16 views

How to turn on the balloon popup with the calculated results of the selected part of the formula?

I accidentally switched it off and could not find how to turn it back on.
1
vote
1answer
38 views

Google spreadsheet lookup

I need to lookup cell values on multiple sheets (tabs in current sheet) and generate a simple report on a sheet/tab named "REPORT" of the current Google Sheet. Sheetname1 Lookup column A for X, ...
1
vote
1answer
20 views

Appending the content of one cell to another cell

I'd like to automatically create the content of a cell, based on the value of another cell. For instance: Cell content: 1337 Cell content 2, automatically created: http://www.superuser.com/1337 ...
1
vote
1answer
25 views

Using a single cell with comma separated tags to tag a column of products

Cell E1 contents has product tags: Sets,Cuff,Bra,Panty,Garterbelt,Costume,Blouse,Skirt,Pantyhose,Corset,Headwear,Lace,Crotchless,Thong,Negligee,French ...
2
votes
1answer
54 views

How do I get multiple driving times in Google sheets without “Service invoked error”?

I keep getting this error. "Service invoked too many times for one day: route. (line 3)." Here is the link to my sample sheet. With script attached ...
1
vote
2answers
43 views

How to filter everything in one column out of another

I have two columns. Column K has a bunch of values, Column L has some of these values. I want to filter everything that appears in Column L out of Column K. How would I do this?
1
vote
1answer
53 views

Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines

I have a Google Sheet which receives information from a google form. I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine ...
4
votes
1answer
260 views

Conditional formatting with multiple criteria for a range of cells

I'm trying to use conditional formatting to highlight a cell if it's value is not empty, AND if it's value is not equal to -------. I've tried every normal formula I use to check if a cell is empty ...
1
vote
0answers
12 views

match and lookup function on google sheets match different japanese kanji

Checkout this google sheets: https://docs.google.com/spreadsheets/d/1dw0v_64uN2lCyB7FxfoBmQ5d1OVIBEemxe5TtPaiUFY/edit?usp=sharing Despite the fact that those two kanji characters are completely ...
3
votes
1answer
34 views

Combine text from multiple cells into a single cell forming a sentence?

I would like to combine text from Cell A2, B2, C2-I2, J2, into a sentence in E2. Same thing would continue down in Row 3, 4, 5, etc. I would want the sentence to end up being "[A2] can be bought from ...
0
votes
0answers
11 views

Can a sheet validate that a local file (referenced in cell) exists?

I think Google Sheets, as with most (all?) browser apps, is sandboxed so this may not be possible but... Is there a way for Sheets to validate a local file path referenced in a cell (file exists? ...
2
votes
1answer
22 views

Automatically populate a table with time values, based on the type of event

I am hoping to find a way using the conditional formatting to find an easier way to have a populated registration time (F), event start time (G) and event end time (H) for each event type as shown in ...
4
votes
1answer
42 views

Validate the URL referenced in cell

Is there any way for Google Sheets to validate that the path/URL (referenced in a cell) is valid? I'd like Sheets to scan a column and mark an adjacent column as true or false depending on whether ...
4
votes
1answer
35 views

Update column with last update of row in Google Sheets… except the header

I've been using this code to find the most recent update of a given row: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var r = s.getActiveCell(); if( r.getColumn() != 5 ) { ...
1
vote
1answer
35 views

Conditional formatting based on comparison with today's date and also with a future date

I'd like to conditionally format cells that will go red if it's past its expiration, go yellow if it's within 3 months of expiring, to remain green if date is clear of expiry. For example: today's ...
1
vote
2answers
47 views

How to check if the value is given in google spread sheet cell

I want to check if the value is given in the cell, start time and end time. It works fine now with the function like this: =if(and(C9 > 0, B9 > 0),C9-B9, 0) But instead of 0 I and to check ...
3
votes
1answer
28 views

Can I use COUNTIF with a range of values (min, max)?

Just wondering if it is possible or which equation to use if I want to calculate certain values between them and return how many meet those criteria. For example: I have values in multiple columns ...
2
votes
1answer
44 views

Conditional format formula to select a previous cells content (current cell -1)

Thanks to "Can Google Spreadsheets conditionally format based on a previous cells content?", I understand how to apply conditional formatting to a preceding cell, if I'm using a simple comparison, but ...
1
vote
1answer
114 views

How to copy related row data from Google Sheets remote tab or Sheet into current Sheet with a script?

I could use importRange, but I want to actually place the values themselves into the cells, instead. For sheets with many rows, a lot of importRange formulas can cause lag and slow down performance. ...
1
vote
2answers
29 views

Google spreadsheet: reformat event data (several dates on one row/event) to create a .csv file (one date per row/event)

I currently have a list of several recurrent events, with dates of recurrence on the same row. I hope to create a .csv file for importing into calendars, so would like to have each recurrent date on a ...
1
vote
1answer
37 views

Filtering a range by another range, custom function just says “internal error”

I'm trying to filter a range by comparing it to another range. I couldn't figure out a way to do this with functions in Google Spreadsheets so I made my own. function FilterByArray(range, ...
2
votes
1answer
19 views

How do I sort a range of filters and functions?

I'm trying to sort a range that is populated by a filter and various functions. Whenever I try and apply a "filter" (the upside down triangle in the toolbar) and try and sort names or numbers from low ...
2
votes
1answer
41 views

Can Google Spreadsheets conditionally format based on a previous cells content?

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it? For example, in the following table (first row/column are titles): | A | B | ...
1
vote
1answer
34 views

How Do I filter this range of multiple rows and columns only by their headers?

I have a range of names with their team names as headers, how do I retrieve the header for a specific persons name? Pic: I have a person's name, and want to find the team they are associated with. ...
2
votes
1answer
275 views

Simplifying Google Form Checkbox Responses into Itemized List

This is more of a Google Sheets question than a Forms question. I want to receive form responses from checkbox questions (which unfortunately cannot be separated into separate rows) and flip the data ...
2
votes
1answer
16 views

Dragging functions through multiple cells with spaces in Google Spreadsheets

I have a function that needs to grab data from 1 row at a time on another worksheet. However on the worksheet the function is on, I have extra spaces and dragging the function down causes it to ...
1
vote
1answer
20 views

Formatting work schedules in an easy-to-process, and easy to enter way?

I'm trying to format work schedules in google spreadsheets in a way that makes them easy to process. I have a sheet that will tell you who is supposed to be here on what day, who was gone, and ...
2
votes
1answer
70 views

How do I set up notifications for when a new sheet is added in Google Sheets?

I am part of a large group of users that share a Google spreadsheet and would appreciate if anyone can help me figure a way to set up immediate notifications specifically only for when someone adds a ...