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

learn more… | top users | synonyms (2)

2
votes
1answer
27 views

Reverse lookup from Google spreadsheet key

I can't figure out where the data in an IMPORTRANGE function is coming from. How can I use the spreadsheet key to find the the original spreadsheet IMPORTRANGE is pulling from?
2
votes
1answer
53 views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
2
votes
2answers
35 views

Conditional Average with a function

I'm trying to average a range but only if one condition is true. A B C 1: A 1 2 2: B 3 4 3: C 5 6 4: D 7 8 5: A 1 6 6: E 8 9 7: E 5 8 What I want is if the char I'm looking for is "A" then I ...
2
votes
1answer
38 views

Split one column into two equal halves

GOAL: Split one column of items into two half-sized columns of items. So if there are originally 1 column by n rows I want two columns by (n ÷ 2) rows with exactly the same items as the 1 x n column. ...
2
votes
1answer
43 views

Auto create sheets within a spreadsheet with formatted columns and cells

What I would like to do is be able to run the script and have it create 4 new sheets (within the current sheet I'm in) with the following formatting in each: 1st row frozen with column headings on ...
2
votes
1answer
62 views

Google drive (sheets) shared file - move ownership - different domains

I'm helping my daughter with her project and started an example of how to create a table using google sheets, which I shared with her. She continued on with it but now I want to remove it from my ...
2
votes
1answer
43 views

How to auto format a phone number with an associated hyperlink in Google Sheets

I don't know if this is possible, as its a bit of an edge case, but it would be amazing if this can be accomplished. I have a spreadsheet of contact information, with fax numbers. At work, we send ...
2
votes
1answer
41 views

How to update values from another cell only once, not with subsequent edits?

I have a cell A1, whose values I change periodically. I have a cell F7:F, I add values to this column from time to time. I have another cell K7:K whose values depend on what's in A1 and what's in F7:...
2
votes
1answer
21 views

Find slope/intercept of last 30 values entered?

I have a column A7:A that has numbers from 1+; I have another column B7:B, that has dates from Oct 31st, 2015 +; I have a column F7:F that has weight values; I have a goal weight of 108.36297798; ...
2
votes
1answer
116 views

Google Spreadsheets CSV Encoding not UTF-8?

I was assuming that Google spreadsheet exports UTF-8 encoded CSVs. However, this does not seem to be the case — my German Umlauts, etc. (äüöß, etc.) are not recognized, i.e., when opening the file in ...
2
votes
1answer
42 views

How to add bulleted list in Google Sheets

I am unable to add bullet in front of any sentence. See below example, I need circle bullets in front of every sentence written in one cell. First Sentence Second Sentence Third Sentence How can ...
2
votes
1answer
41 views

Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now ...
2
votes
2answers
46 views

Join several columns with a single formula returning an array

This is the opposite of my question Split a column of strings with a single formula returning an array. I have several columns, say, A-K, which I want to join into one comma-and-space separated column ...
2
votes
2answers
29 views

How to recombine text into one line in Google Sheets

=index(ImportXml("https://www.google.com/finance?q=ibm", "//div[@class = 'companySummary']" ),0,1) Put that anywhere in a Google sheet and it will give you a cell with a block of text. This text is ...
2
votes
1answer
143 views

Getting COUNTIFS to work in complicated Google Sheets formula

I'm having trouble with COUNTIFS on Google Sheets. I've been able to use SUMIFS and now I need to use COUNTIFS. I tried to do a Find and Replace, changing SUMIFS to COUNTIFS, on what I thought were ...
2
votes
1answer
113 views

Is there a way to have a Slack overlay in Google Sheets?

Google Sheets lets you chat with other people actively using the sheet, but it has no history and overall feels like the simplest thing that could work. Slack, by contrast, seems like everything you ...
2
votes
1answer
54 views

Custom Google Spreadsheets shortcuts : move selected row(s) Up/Down/Top/End

As the title says, I do move rows a lot and the lack of default shortcut complicates things for me... The drag option isnt practical when I need to move some rows to the bottom of a huge sheet!! I ...
2
votes
1answer
22 views

Time query in Google Spreadsheets

I've started using Google Spreadsheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the ...
2
votes
1answer
209 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
28 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
60 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
103 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
33 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
22 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
34 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
150 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
145 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
63 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
34 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
84 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
73 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 https://docs.google.com/spreadsheets/d/...
2
votes
1answer
59 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
43 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, filterRange)...
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
127 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
102 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
210 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
401 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
81 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
234 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
61 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
62 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
327 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
111 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
156 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
52 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
249 views

Multiple cells with same drop-down list, but disallowing duplicate selections

I'm looking to create a few drop downs in my spreadsheet where they share the same list of choices, but you can't select the same option twice. This is for choosing first, second, and third choices ...