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

learn more… | top users | synonyms (2)

2
votes
2answers
1k views

How to write a logical function that checks several columns and rows

I have three columns in my Google Spreadsheet: C (Dialog) - D (Declined) - E(Answered) All the columns are dates. Example: C D E 14/02/2012 28/02/2012 ...
2
votes
1answer
938 views

How to put the name of a cell into another cell in Google Spreadsheet

In Google Docs Spreadsheet, is there a simpler way to put the name of a cell reference into another cell than this? =concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
2
votes
2answers
1k views

How to count cells that are below a certain date and check if other cells in the current row are empty?

I need to count how many cells in a specific column are below a certain date. Also, at the same time, the counter should only be increased, if some other cells next to each cell being counted are ...
2
votes
2answers
4k views

Hyperlinks in Google spreadsheet docs

In Google spreadsheets, it is possible to add a hyperlink to a cell as: =hyperlink("www.google.com";"search the web") This will show the text search the web which will hyperlink to Google. ...
2
votes
1answer
6k views

Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
2
votes
1answer
315 views

Getting a new “publish to web” URL for a published Google doc

When I publish a Google spreadsheet to the web, it generates a URL that I can share with others. I know I can stop publishing. When I re-publish, the URL is the same. I no longer want ALL viewers who ...
2
votes
1answer
134 views

Can I add a search box that will open a new page on some 3rd party site to Google Docs?

I'm trying to make a document/spreadsheet containing a few resources I regularly use and perform searches in. Can I create something I can perform searches directly from a document that will open up ...
2
votes
2answers
4k views

Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
2
votes
1answer
15 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
2answers
21 views

Don't disturb formulas on Google Sheets when cutting and pasting

I have the formula: =IF(Q5=time(0,0,0),1,0) in cell A3. It works fine, but I need to "cut" and paste the contents on Q5 elsewhere and then Google Sheets "updates" the formula changing Q5 for the new ...
2
votes
1answer
29 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
31 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
29 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
37 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
44 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
33 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
34 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 ...
2
votes
1answer
20 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
32 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
31 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
45 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
26 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
99 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
97 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
19 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
128 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
23 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
42 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
89 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
28 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
32 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
25 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
93 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
92 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
51 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
32 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
71 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
62 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
51 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
20 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
102 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
79 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
172 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
282 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
61 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
170 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
51 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
55 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 ...