Tagged Questions

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

learn more… | top users | synonyms (1)

0
votes
0answers
15 views

Using SUMPRODUCT with ARRAYFORMULA in Google Spreadsheets

I have the following random data in sheet one A orange D yellow C yellow B orange B orange C blue A yellow D blue C orange C blue In sheet two I use the following query function: ...
0
votes
0answers
24 views

How to make my Excel formula work when transferring it to Google Spreadsheets

I am trying to convert an Excel program that tracks repairs inputed by staff and updates a master file. I had the formula working and once I had transferred it to Google Spreadsheets it stopped ...
0
votes
1answer
12 views

How do I have the choice made in an in-cell drop down menu affect the cells next to it?

I have a contact sheet with names going down the list and contact info in the cells to the right of each name. On a separate signup sheet, I used data validation to create a drop down menu with the ...
2
votes
1answer
38 views

How to make SQL JOIN of two tables with Google Spreadsheet?

I am just figuring out how to achieve an apparently simple problem. I use to have data as in database tables in my spreadsheets. It's simple data but I often need to use =QUERY function and would be ...
1
vote
1answer
16 views

Problem adding Image to hyperlink in Google Sheets

I want an image to be my hyperlink in Google sheets. Here is my code: =HYPERLINK("google.com/de", IMAGE("http://s5.favim.com/orig/69/hi-love-quote-start-Favim.com-658946.jpg")) The image shows up ...
0
votes
1answer
28 views

Sum cells according to cell colour

I want to sum cells only with a specific cell colour. I'm using this code: function sumWhereBackgroundColorIs(color, rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); ...
0
votes
0answers
10 views

Where is the official comprehensive reference page Google spreadsheet URLs

Google Spreadsheets URL Syntax and Display Options? was a question that was answered by referring to an old page: http://code.google.com/apis/spreadsheets/data/2.0/reference.html#Parameters ...
0
votes
1answer
19 views

Moving from one Sheet to another in Google Sheets

I am using a Google sheets to organize an upcoming project. On the first tab I have every task listed, and who the actor should be for that task. I plan on sharing this sheet with my team... Is it ...
0
votes
1answer
19 views

How to merge 2 tables into one?

I have in my Google Spreadsheet two separate tabs containing each one a table as showed hereafter: Tab 1 Tab 2 ______________________________ ...
1
vote
0answers
13 views

Disable Drag & Drop Google Sheets

DEFINING DRAG & DROP: In google sheets if you have a cell or cells currently selected you can perform an operation on them. One such option is to move your cursor to the edge of the selection ...
0
votes
1answer
16 views

Show only own record to Google Spreadsheet user and hide data from other users

I'm organizing an event, so I set up a table for people to sign up using Google spreadsheet. For concern of personal information privacy, I'd like to make it only be able to add a new record, for ...
2
votes
0answers
25 views

Appearance of Forms I never created in Google Drive

I have some Google Sheets file which I copy and change monthly. I never used Forms, but they are appearing all the time in the Drive and are related to this G.Sheets file as you can see on the ...
2
votes
1answer
24 views

Collect data from dropdown list

I'm making an expense spreadsheet, to keep track of all my expenses. I have made a dropdown-list with different category (Rent, Food, Others etc.) these are put into C2 to CXX, in the next cell D2 to ...
1
vote
1answer
14 views

Add FILTER condition if parameter is present

I have on the top of the page some filtering parameters (see $H$1 below). They may be left blank, and if so, they should not be used. How to use then conditionally in FILTER?? The following don't ...
1
vote
1answer
22 views

ArrayFormula to compute Running Average for groups of rows

Is it possible to write an Array Formula which calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I ...
2
votes
2answers
48 views

Find the closest value (equal or greater) in a range in Google Spreadsheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected. ...
2
votes
0answers
32 views

How do I refresh script results when a cell color is changed?

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. ...
6
votes
2answers
51 views

How to Turn Leftover Minutes into Hours

I have a spreadsheet with two columns that count hours and minutes, respectively, with sums to totals at the bottom. I would like to make it so when the minutes reach sixty, it automatically subtracts ...
1
vote
0answers
12 views

Pull data from a cell in one Google Sheet to another cell in a separate Google Sheet [duplicate]

I have a Google Spreadsheet with quite a few sheets in it. I am trying to pull information from that sheet and put that info into another sheet in a different Google Spreadsheet. I have googled this ...
0
votes
1answer
43 views

Counting occurrences in Google Spreadsheet

I've been brought on last minute to sort out tent allocation for a festival of about 1500 pax. Their ticket sales are a spreadsheet on Google Drive. I have never done this kind of thing before (I'm ...
1
vote
0answers
29 views

Auto import CSV into existing Google Sheet

I understand how to upload and convert CSV files to Google Sheets -- but how can I set up an automatic upload of all new data from a CSV file into an existing sheet? I have a CSV file that lives on ...
2
votes
1answer
16 views

Duplicate then concatenate in Google Spreadsheets

How do I join a to both sides of : to result in a:a? I could use something like =join(":","a","a"), but in this case, my "a" is another formula and it is very long. I'm trying to find a way so I ...
0
votes
0answers
16 views

Listing live stock prices with 15 minute intervals in Google sheets

I use google Sheets to keep an eye on stocks. Their live data is all I need. I would like to have a sheet that adds a new row with the stock price and time every 15 minutes. Is this possible with the ...
4
votes
2answers
81 views

How to move sheets in Google Sheets with the keyboard?

I want to rearrange the sheets in my workbook. I have many sheets and moving the last one to the front is very slow with drag and drop. Ideally I want a command that will select a sheet and move it to ...
3
votes
1answer
51 views

What is -Ls 1000 in Google sheets

I've been working with inventory spreadsheets in Google sheets. I have many SKU's in my inventory and when pasting or typing them in, the are interpreted as text and are left justified in general ...
3
votes
1answer
47 views

Can you link directly to a new copy of a Google Doc or Spreadsheet?

Is it possible to craft a URL (or bookmarklet) so that when clicked it opens a fresh "copy" of an existing Google doc or spreadsheet? Apparently it used to be possible by appending &newcopy=true ...
1
vote
1answer
32 views

How to extract IP address of the host into a row?

It seems it's possible to How to extract website title into a row?, but how to extract IP address of the host into a row? So for given any hostname (e.g. webapps.stackexchange.com) it should return ...
0
votes
0answers
45 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 ...
-2
votes
0answers
21 views

Automatically set “last updated time" in a cell? [duplicate]

I want to be able to have cell E6 automatically update with the date and time when ever a change is made in Column 3. This is my script so far; can anybody help me? function onEdit(event){ var ss ...
-2
votes
0answers
30 views

Return an adjacent cell for each match of a string

I have a row of cells like this: | Name1 | String | | Name2 | | | Name3 | String | | Name4 | String | | etc. | ... | I would like to return all of the Name values if the cell adjacent to ...
7
votes
2answers
666 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
71 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
39 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
52 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
52 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
34 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
29 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
49 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
36 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
42 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
26 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
31 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
48 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 ...
2
votes
1answer
37 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
30 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
48 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
50 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
47 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
68 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
26 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 ...