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

learn more… | top users | synonyms (2)

2
votes
1answer
165 views

How to automatically wrap text to new responses from a form in a Google Spreadsheet?

I had a working Google Spreadsheet where when new responses were filled in on the form, they would automatically get textwrapped on the Form Responses sheet. Now recently I had to remake this ...
2
votes
1answer
27 views

What is the difference between the DELTA and EQ functions?

The new Google Sheets includes the DELTA function, which Compare[s] two numeric values, returning 1 if they're equal. How does this differ from the EQ? From what I can tell, the EQ function ...
2
votes
1answer
53 views

Google Spreadsheets: Sum values for each month

I would like to sum the values that match each month in a spreadsheet. The spreadsheet looks like this: 2015-12-10 15 2015-12-19 10 2016-01-02 15 2016-01-05 25 And I want to obtain this: ...
2
votes
2answers
93 views

Google Sheets/Forms/Script: How to send an email then replace “@” with “#”

I am trying to create a script where the user gets an email (column 1) with the body being column 2. However, after that, I want to replace the "@" in the email with a "#" so that they do not get the ...
2
votes
2answers
30 views

Google Sheets LEFT function trim text generated by IMPORTHTML function

When I use this formula to import data =iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1)) The result is Sector: Industrial Goods. I'd like ...
2
votes
1answer
21 views

Searching a value between two columns on a reference sheet

I have my main sheet that looks like this: The compromise column (Column F) bases it's value on another sheet. To compute it's value, we check if the value of the Basic Tax (Column B) falls equal ...
2
votes
1answer
33 views

Google Spreadsheet Inconsistent Datetime Format

When we push date and time values to a google spreadsheet in the format d/m/Y H:i a (eg. 25/12/2015 1:43 pm) the date and time is displayed inconsistently, see below: Any idea why the format is not ...
2
votes
1answer
27 views

Why does Google Spreadsheet use days as unit of time when entering a number?

When using Google Spreadsheet today, I noticed that when entering a date as a simple number sequence (e.g., 151127), and choosing date as format, it ended up being a completely different date than the ...
2
votes
1answer
111 views

Conditionally substitute cell contents with Unicode characters

Is it possible to conditionally substitute a cells contents, with a Unicode character i.e. rather than colouring all '1' cells green, swap the '1' for another character instead? Specifically, I want ...
2
votes
1answer
110 views

Combine two columns into a list of all possible combinations of entries

I have two columns such as: a 1 b 2 c and I need to combine them like: a 1 a 2 b 1 b 2 c 1 c 2 Is it possible with a formula?
2
votes
1answer
56 views

Auto calculate and display timestamp in a specific row and column

I have a spreadsheet with a formula that works to display the timestamp in the same row, but is there a way to set a column at the same time so I don't have multiple time stamps displaying along that ...
2
votes
1answer
33 views

Why can't I refer to another cell with the value comparator field?

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already. From what I could gather, if I want to apply a conditional formatting based on the value of ...
2
votes
1answer
79 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 ...
2
votes
1answer
18 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.
2
votes
1answer
66 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 ...
2
votes
1answer
52 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 ...
2
votes
1answer
21 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
124 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 ...
2
votes
2answers
93 views

Add the numbers in rows where a string contains a given word

I have two columns a and b a contains a set of text and b contains a set of numerical that relates to each row in a I want to create a function that will search column a for substring value and ...
2
votes
1answer
184 views

In Google Sheets' filter views, can I filter columns non-cumulatively?

I have been putting together a spreadsheet in Google Sheets that includes various data/information relating to the hundreds of characters in a novel I am reading. Two of the columns relate to the ...
2
votes
1answer
341 views

In a Google Spreadsheet, show all combinations for a selection of columns

I have a sequence of letters, in a Google Spreadsheet: | A B C D E F --|---------------------- 1 | U Y 0 4 X N 2 | H X Y 3 | W The first row contains a ...
2
votes
1answer
69 views

How to sort by date with Google Spreadsheet?

Consider the following text in a single column in a spreadsheet: AAPL 2015 Dec 18 C @ 185.000 AAPL 2016 Mar 18 C @ 160.000 ... How to sort by date? I tried extracting the date using regex, but it ...
2
votes
1answer
205 views

Google Sheets: COUNTIF with dynamic criteria

I would like to count the number of occurrences of a value in one sheet based on the value of a cell in another sheet. I would like to use this formula: COUNTIF(Sheet1!$A$2:$B:130, =A1) Where A1 on ...
2
votes
1answer
55 views

How to display dynamic range of data from another sheet, but while transforming one column

I have a sheet of data in the form: Date Val 2015-08-05 10 2015-08-09 13 2015-08-14 4 I am trying to display this data in other cell such that it looks like this: Date ...
2
votes
1answer
59 views

How to perform a Vlookup with search column dynamic depending on input in another cell

I have the following table. What I want to do is as follows. User selects either Minor, Medium, or Major and that specifies which column is used. User then inputs a number 1-100 and the chart ...
2
votes
1answer
283 views

Clamp Value in Google Sheets

I'm trying to clamp a value between a minimum and maximum value. If this were a formula, I'd expect it's usage to be =CLAMP(value, min, max). From what I know, there's two approaches, nether of ...
2
votes
1answer
17 views

Formula counting based on two conditions

I am struggling with a formula in Google Spreadsheet. Here is what I want to achieve: If a column in a book is not null and if another column in another book is having a specific value, then counting ...
2
votes
1answer
103 views

Extracting GICS codes from Fidelity website

This is the website: https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=aapl&type=o-NavBar I'm trying to pull out the following two pieces of data into a Google Sheet: ...
2
votes
1answer
151 views

Convert one column of multicolumn output REGEXREPLACE from string to number

In an expression of the form (one column in, two out): =ARRAYFORMULA(REGEXREPLACE(A1:A100,...,{"$1","$2"})) How can I convert just one of the output columns from text to a number (like VALUE)? The ...
2
votes
2answers
47 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
2
votes
1answer
913 views

Clear range of cells

I'm trying to make a script for Google Spreadsheets (which is my first time), by clearing a range of some cells. So: for example I want to clear D5:D12 but not D13 and D14, but then again I want to ...
2
votes
1answer
31 views

How to compute the maximum of Column B after having filtered by the contents of Column A?

I have data in two columns (say A2:B20). To get what I want, I have to filter by the first column, and then get the maximum value in the second column of the filtered array. Can this be done without a ...
2
votes
1answer
147 views

Import Google Contacts to Google Spreadsheet

Google notice me : You do not have permission to call getContacts (line 3) Here is my code: function importFullName(input) { var CONTACT = ...
2
votes
1answer
210 views

Create a 7 day running total and running average using ARRAYFORMULA

There's a question on this forum that asks how to get a 7 day running total and running average. It's available here and it reads like this: I have a Google Spreadsheet tracking figures day by ...
2
votes
2answers
75 views

Delete duplicate text within a cell

I've got cells in a column populated with data pulled from another tab using A1= Results_ES!C1 and A2= Results_ES!C2 etc. Some of the data is duplicated whithin the cell, e.g. Cell A1= Black Black ...
2
votes
2answers
239 views

Can I automatically email the row contents I enter in Google Sheets?

I have a Google Spreadsheet where I enter a row of data with any changes to my office schedule for the day. Is there a way to automatically email this row of data (not a link to the spreadsheet, but ...
2
votes
1answer
230 views

Google Sheets: Format number as Mhz/kHz, or MB, kB etc

I want to make a spreadsheet with Google Sheets to compare microprocessor's specifications. One column will show processor speed, another will show program space, another for RAM space and so on. ...
2
votes
1answer
164 views

Concatenate inside ImportRange inside Query

I found this formula: =QUERY(IMPORTRANGE("key","A:Z"),"SELECT Col1, Col2, Col3 WHERE Col1 contains 'value' ORDER BY Col1") Here and I'd like to add a concatenate function where 'value' is taken ...
2
votes
1answer
39 views

Applying a Formula to All Cells in a Column (Even New Cells)

I'm using Google Spreadsheets and currently, this is what I have in J2: =iferror(H2/G2,0) I know I can double click the square in the bottom right and it will apply to every cell in the column but if ...
2
votes
1answer
67 views

Subtract values in a list that fit certain criteria within Google Sheets

I've got this sheet where I do some basic accounting, and I want to be able to input the balance in my pocket every once in a while. The idea is that the expenses I record until the next balance will ...
2
votes
1answer
103 views

index() match() join() I want to show all duplicate results disregarding their order

I'm trying to write a formula to make volunteer-task scheduling easier. In the database I have lists with volunteer names that have chosen specific tasks and specific timeslots. I wrote a formula to ...
2
votes
2answers
230 views

Find and replace text in Google spreadsheet

I have a string of data in one cell: "Membership and Volunteer Crew - do you like getting to know lots of new people? | Sauna Crew - Spend time sweating and supervising our sauna activity. | ...
2
votes
1answer
276 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 ...
2
votes
2answers
63 views

Autosum entire column

On Google Sheets, I enter my sales for the month in Column F. How can I get column F to Auto Sum so that the total always appears below the bottom entry?
2
votes
1answer
1k 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 ...
2
votes
1answer
63 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 ...
2
votes
1answer
123 views

How to put the edited date of a cell into another cell in Google Spreadsheet [duplicate]

In Google Spreadsheets, is there a simpler way to put the edited date of a cell into another cell. I've set a data validation list: ACTIVE COMPLETED ONHOLD TESTING BUGFIXING to F column. If the ...
2
votes
1answer
3k views

Filter by Date Range in Google Spreadsheets

The filtering in Google Spreadsheets seems to only offer text based solutions. Is there any ability to filter cells by date ranges like any of the following: Today Yesterday Last Week Since ...
2
votes
1answer
107 views

How to enter a date in a cell and have the date for one week later automatically fill another cell?

I want to be able to type in a date in one cell, and then automatically have the date that is seven days from the date I typed appear in another cell in that same row under a different column. How do ...
2
votes
2answers
62 views

How to add adjacent cells in Google Sheets

I am looking for a way to add up cells under a name. For instance, I am trying to tally all of the assists the character "Annie" got in a few different games of League of Legends. I am looking for ...