One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

0
votes
0answers
319 views

Complex Index and Match with three criteria

I need to create a formula that will match three criteria in a range on another sheet, and where they match, return the value in an adjacent cell. The problem I have is that one of the criteria is a ...
7
votes
2answers
977 views

Which formula extracts the domain name from an email address (bob@example.com -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: bob@example.com → example.com
1
vote
2answers
762 views

How do I apply formatting to a cell based on comparison with another cell?

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns: A (Breed) | B (Male) | C (Male Goal) | D (Female) | E ...
2
votes
1answer
46 views

Create a public spreadsheet which requires users click the “edit” button to mutate it?

How do I customize a public Google Spreadsheet like this? It needs users to click the "Edit" button so that they can edit:
1
vote
1answer
95 views

COUNTIF in Google Sheets that checks 'if' a string is in one column before counting in another column

I'm trying to use the COUNTIF function in Google Sheets. The Goal I want the 'dashboard' sheet to display the count of items in column F submitted to the responses sheet 'if' a specific string ...
2
votes
0answers
112 views

Batch Hiding Function in Google Script

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array. I then ...
3
votes
1answer
291 views

Conditional Formatting: Minimum Value in Row

The goal Apply conditional formatting to the minimum value in a row of a Google Spreadsheet. What I've tried Under Conditional Formatting, using "Custom Formula Is" =MIN(D3:L3). The result ...
2
votes
1answer
48 views

Complex Formula in Google Spreadsheet

I have a 'dashboard' sheet and a 'log' sheet. I'm trying to pull data from the 'log' sheet and perform calculations on the data before displaying it in the 'dashboard' sheet. The 'log' sheet will have ...
2
votes
2answers
252 views

How can I filter a full table based on only unique values in a single column?

How can I deduplicate a table (all rows and columns) based on the values in column A? Example: What I want after deduplicating: Basically, I only want unique values in column A, but I want all ...
1
vote
1answer
207 views

Filter columns in Google Spreadsheets based on fill colour

I've got a long list of items that I need to check. I use a traffic light system to track where I change the cell colour of the cells in the progress column: red = haven't done yellow = in progress ...
3
votes
1answer
86 views

Update Random value only on page refresh

I am using =RANDBETWEEN(1,100)function to generate a two digit random integer. But I want these values to refresh only on the page refresh, whereas currently they are changing whenever any cell is ...
2
votes
0answers
37 views

Automatically adjust corresponding cells when using “=query”

I just learned how to use the =query function in Google Sheets, pulling information from one sheet to the next when a specific condition is met. =query('Sheet1'!A:Z, "select B, D, E, F, G, I, L where ...
1
vote
1answer
79 views

How to get more than 1 question to populate the same field/column in the Spreadsheet?

I am looking for some way to get the submitted answers to 2 or more questions to populate in the same column in the response spreadsheet, but on the next row. e.g. When gathering data from ...
4
votes
1answer
64 views

How to get the +1's of a website?

I've a spreadsheet with a lot of contact information. In order to sort the data, I want to rank them according to the +1's given on their website. Is it possible to get the +1 count of a website ...
3
votes
1answer
376 views

How to make Google Spreadsheet editable only at certain times

I need to create a document (something like a Google Spreadsheet) where multiple users could insert their names in, but within a limited time span. They could edit the document only from Monday (8pm) ...
1
vote
0answers
136 views

How to prevent user from downloading my Google Sheets

I have made a few useful spreadsheets for my company. I want to put them on Google Sheets, so my colleagues can use it online to make inputs and get results. But I do not want them to know the ...
1
vote
2answers
109 views

Show cell value on click

I have a formula in a cell to calculate a value. But I want the value to be shown only when the user clicks on the cell. I looked around but there seems to be nothing by default.
1
vote
1answer
70 views

Circular Dependency Error, Blank when 0

I have tried both of these in Google Spreadsheets and continue to get circular dependency errors. I am trying to show blank when the result is 0. New values are entered daily and want to have the ...
0
votes
1answer
73 views

How to turn cell yellow if date in cell is between 180 and 365 days in the past

In Google Spreadsheets how do I use conditional formatting to turn a cell yellow if the date is between 180 and 365 days in the past?
1
vote
1answer
101 views

A more complicated Countif (Google docs)

I have a simple countif:= Countif(A1:A5,"statement"). And I want it to count the statement n times if it appears n times in my cell A1 (plus of course the number of times it appears on the other ...
0
votes
1answer
39 views

Auto Insert Address on adjacent cell

I am trying to create a spreadsheet which enters address automatically on adjacent cell when I type in the company name. The script will be as I type in company name in cell A1, it will search for ...
1
vote
2answers
36 views

Lock formulas in Google Spreadsheets

I'm creating a table for a friend to input information but he is not very good at computers. I had him work on a table in Excel and locked the page and this worked great but now I want to check his ...
-1
votes
1answer
88 views

Google sheets query with sum and group by

I am trying to have a query that selects certain columns checks a date criteria, sums up total and has a group by criteria. My formula is not working. can you guide me into what i have done wrong? ...
0
votes
1answer
4k views

Error: Circular dependency detected

I am very beginner to google scripting. Here I need to count the number of times the word "FREE" occurred in my sheet. But my function Returns me an error "Error: Circular dependency detected" Please ...
0
votes
1answer
693 views

Copy cell from cell to another sheet if row contains x

I'm new to sheets, I have a sheet with 10 pages. I need to auto fill sheet Report if sheet David contains "1" in column "i". I need to copy cells C14 - C34, J14 - J34 and L14 - L34 only if there is ...
0
votes
1answer
72 views

Wildcard in Google spreadsheet

How do I convert the following formula to do wildcard for "Florida"? =Sumproduct((Sheet1!B:B="Florida"),Sheet1!A:A) Ideally, I like the formula to do this: ...
1
vote
1answer
43 views

How to lock formula when adding column in Google Spreadsheets

I’m trying to keep an average of the 5 newest values of a data set: Average($d$1:$h$1). If I add a new column to the left of D for new data, the formula changes to ($e$1:$i$1). How do I prevent the ...
1
vote
1answer
28 views

Lookup using a key from a dropdown cell

I have a dropdown that selects from several text "keys," like so: List of items: Pathetic,Untrained,Novice,Adept,Master,Amazing I'm trying to use these keys to run a lookup on a separate sheet, with ...
1
vote
1answer
24 views

List all records with certain criteria

Help. I just cannot get this going even after 2 hours of searching Google. I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
2
votes
1answer
49 views

Problem with SUBSTITUTE function in Google Spreadsheets

I'm having problems using the SUBSTITUTE function as part of a formula. On it's own, SUBSTITUTE returns the desired output (i.e. in cell O3), but when combined in a formula, 0 is outputted (i.e. cell ...
0
votes
1answer
99 views

Import a CSV file to Google Spreadsheets

I am trying to upload a CSV file and importing it to Google Spreadsheets. The file contains the following: hi,hello\r\nwelcome,to\r\npeaceful,world What I expect in the Google spreadsheet is: ...
0
votes
2answers
71 views

How to sum values by month

I'm trying to have my spreadsheet to sum up values by month. Note that the dates are not organised chronologically. I've tried the solutions showing here but they are not working for me. I'm not ...
0
votes
0answers
57 views

Equivalent of Google's ImportXML in Excel Online

I wonder what is the equivalent of Google's ImportXML in Excel Online? I basically need my workbook to get an external XML data, and refresh it every X minutes.
1
vote
1answer
29 views

Counting Instances in Google Sheets

At my school, we have a Google Form for recording classroom discipline. The form generates the standard spreadsheet. I want a list of how many times each unique student has received a disciplinary ...
3
votes
1answer
131 views

How to extract website title into row?

How to extract title (or other meta descriptions) of the website into row using Google Docs? E.g. www.google.com
1
vote
2answers
319 views

Issue with Custom number format in Google Spreadsheet

I have a number cell formatted in this way: #,#" days";. Nevertheless, if I write 1.5the output is 1 days even if the content is still 1.5. Otherwise, if I write 1,5 the output will be as it is ...
2
votes
1answer
114 views

Can I subtract a cell value in another cell that already have one in Google Spreadsheets?

Let's say that I have two cells A1 (time needed) and B1 (time spent). I want that when I enter a value in B1 the value of A1 will be reduced automatically. I want that in some way I could enter in ...
3
votes
0answers
51 views

Track changes in read only shared Google Docs

I have shared a spreadsheet, anyone having the link can see it. Since it is a spreadsheet I update daily, I want to facilitate the others recognizing the last changes. However, revision history is ...
0
votes
1answer
174 views

Show all hidden rows and columns when I open a Google Spreadsheet

I want to un-hide all the hidden rows and columns when I open the spreadsheet. How can I do that for all sheets?
0
votes
1answer
292 views

Is there any way to use a formula in a Google Sheets filter view?

I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, ...
4
votes
2answers
154 views

Is there a way in Google Sheets to order blank cells first?

When I sort a column in Google Spreadsheets, the blanks always seem to sink to the bottom. Can I have rows with blank cels be organized first / highest? Alternatively, is there a function wherein I ...
0
votes
1answer
147 views

Return value in column A in preceding cells in column B using an Array Formula

I've been looking for a solution to this for days now. How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in ...
2
votes
1answer
176 views

How do you extract a number from a URL as raw text?

So I have a list of Google+ profile links in a Google Sheet https://plus.google.com/112337650380234682548/ How can I extract the number on the end in a seperate column? I've tried ...
1
vote
2answers
116 views

Calculate time values in a single Google Spreadsheet cell

Is there a way to add two times, or durations, in one line in a Google Spreadsheet? Something like: =1:14:25+0:17:12 I know that I can use the time command in-line, and that I can add times from ...
0
votes
0answers
181 views

How to get Google Spreadsheets to auto-update a reference to another sheet

Referencing: How can I get Google Sheets to auto-update a reference to another sheet? I am trying to do the EXACT same thing for the EXACT same reason. I assume this is a VERY clever work around but ...
0
votes
0answers
17 views

OR function doesn't work inside FILTER [duplicate]

When I use FILTER(A2:A; OR(C2:C >0; D2:D >0)) I get different range intervals error, but when I remove the OR it doesn't return an error. So why OR function can't be used with FILTER function?
2
votes
1answer
46 views

Google Spreadsheet add rows until marker

I have a spreadsheet of the form ItemA Subitem1 5 Subitem2 4 Subitem3 7 <potential marker> ItemB Subitem 4 I want to sum the values next to Subitem to a total next to ItemA, ItemB, ...
2
votes
1answer
66 views

Consolidate List of Similar Data into Single Row in Google Spreadsheets

I have a Google Spreadsheet that lists donations given by constituents with a unique account number. Several constituents have made more than one donation in a given period of time listed separately. ...
0
votes
0answers
290 views

Countdown timer list

Can someone check the javascript code and see what can be done. I am struggling to add B2 time to the start time I think i need to convert B2 time to milliseconds and them sum with the start time to ...
1
vote
1answer
55 views

See last modification in Google Drive/Spreadsheets

In my Google Drive I see when a spreadsheet was modified: How to go about seeing what the modification was?