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

learn more… | top users | synonyms (2)

2
votes
1answer
37 views

Conditional Filters Formula in Google Sheets

So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell. So ...
3
votes
2answers
45 views

Joining Two Tables and Merging Cells

Say I have list of users and their info. Each user can have multiple accounts, and the "paid" status of each of those accounts may be "no" or "yes". A user may have one or two paid accounts. My first ...
1
vote
0answers
23 views

I created a button in a panel (dialogue window), when I push the button I'd like to appear a new panel (dialogue) window

I created a button in Google Spreadsheet. When I push the button a custom window appears. In that window I have two buttons, one to close and one to see a new window. Everything goes well except the ...
3
votes
1answer
50 views

Can I use Google Chart Gallery in Google Spreadsheet?

Google Spreadsheet has column charts but I want to use "drawAnnotations" like this (Annotations). Column chart does't show bar value as "drawAnnotations" on top. My chart included these; First ...
1
vote
1answer
23 views

Why does not the picture in column A move with the sorting A to Z of column B?

I'm using Google Spreadsheets, first column is picture then B column is name. When I want to sort it A to Z, all the names get in alphabetical order, but the picture stays in the same position.
2
votes
1answer
31 views

How to sort Google Sheets columns by row height

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet ...
3
votes
1answer
36 views

In Google Sheets, can you apply a regex to a generated range?

I have a list of UK postcodes, looking roughly like this. Entries may not be unique. A1 1AA A1 2BC A1 3PN B2 4XY B2 5LH C3 6NJ They're in a range named Postcodes. I've set up the following ...
2
votes
1answer
25 views

Formula which will grab specific cells and concatenate them together but only if they are not blank

So I have this formula currently, but it breaks when nothing is in any of the cells. I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE. So my question is, am I ...
0
votes
0answers
17 views

Extraction of integer value from dropdown list inside the same cell?

Using Google Sheets. In one cell, I have a dropdown list that is generated from a concatenation of integer ID column and name column. To get the value of the integer from the selection, I have another ...
3
votes
1answer
37 views

How do I select my highest rated beer/s in a pivot table?

I'm using Google Sheets. I have a table of ratings of beers: |---------------------------------------------------------------------------------| | Beer | Brewery | Style ...
2
votes
2answers
54 views

Turn a table into a list of row/column/value tuples

I want to turn a table like this | | apple | banana | chia | |----------+-------+--------+------| | antelope | 1 | 4 | 2 | | bear | 6 | 3 | 9 | | cheetah | 8 ...
1
vote
1answer
23 views

For range X, when empty grab column A and list them in a single cell, coma separated

I'm compiling stats for our Boom Beach team, which means I have to log attacks by each players for each operations. An operation is launched every day and all players should perform their attack in ...
2
votes
1answer
48 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
1answer
34 views

Forcibly format column as Date in sheets onOpen() with Javascript

I have a simple sheet that gets some date data submitted via a form. The form works great, data is collected, but I have to click on the column, and do Format → Number → Date in order to get dates to ...
1
vote
0answers
17 views

Ensure results stay in redesignated column and don't migrate back to original column

Before updating my spreadsheet to fetch new information from the provided scripts, I insert a new column in between the input column (A) and the results column (B). I want my new results to be next to ...
1
vote
0answers
31 views

Google Spreadsheets SUMPRODUCT/COUNTIF in Arrayformula

Actors\Genre 1 2 3 4 5 6 7 A B C D I need to get the counts of the movies by genres acted by the actors in the column and the movie data is in another spreadsheet, "data". I tried the formula ...
0
votes
1answer
35 views

Google Spreadsheets updating after every change, which is too often, slowing everything down

I have a lot of independent functions and information in my sheet, and every time I make a slight change somewhere in the sheet, it seems like the entire sheet is being saved and re-updated. So every ...
2
votes
1answer
24 views

Can't filter a column to see all cells containing `@` in Google Spreadsheets

I'm trying to filter a column to show cells that contain @ (as in an email address) but when I put @ in the filter box it comes up with a blank. Any ideas? An example spreadsheet here. Here is ...
2
votes
1answer
89 views

Generate a formatted Google document from spreadsheet

As the title says, I have data (Q & A grouped by subject) that I have to export to a document file. As shown above, the source data on the left should be formatted like the example on the right ...
1
vote
1answer
15 views

How to consolidate information from different sources in Google Sheets

So I have a Google Sheet with several sheets (tabs) in it, like so: Sheet 1 - Weapons Legend A B C D A = Date 5/3 John Sword 10 B = From 6/3 ...
3
votes
1answer
48 views

Is there a way to force someone to enter a non-empty value/string in a cell in Google Spreadsheet?

I want to make a certain cell to be mandatory in a Google Spreadsheet. Can this be done? I looked through the Data > Validation and the criteria does not have anything for checking null.
2
votes
1answer
27 views

Get unique values from range of cells

Let's say I have a column of values (e.g., first names). There may be some duplicate data (some first names are very common). How can I get the unique values? In SQL I would do SELECT DISTINCT ...
2
votes
1answer
21 views

How to apply formula to multiple cells without it adjusting part of the formula

I'm having a little bit of a problem and was wondering if your expert knowledge could help me out. Formula in question: ...
2
votes
1answer
48 views

Formula in Google Sheets to lookup contents of another cell

I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...
2
votes
1answer
18 views

Adding an overtime column to a Google Sheets template that includes the overtime rate in the total

I would like to use this template to log bi-weekly hours. It is almost perfect except that there is no column for overtime hours. How would I add an overtime hours logging column that would add the ...
2
votes
1answer
21 views

How to work out the price of shipping an item in Google Spreadsheets

I'm currently trying to build a formula to work out a problem I have. What is the problem: Right now before I can send an item out, I need to work out how much that will cost me. This is done by ...
0
votes
1answer
49 views

Google Form keeps changing Spreadsheet cell entry

I seem to be having a bit of a problem, I'm using Google Spreadsheet and have created a form that people would be able to fill in online. It sends that data to a sheet called 'Raw Entry Form', I then ...
2
votes
2answers
49 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. | ...
1
vote
1answer
25 views

Complex Sum Function in Google Sheets

Apologies if this is lame, but I can't get a proper value using =SUM to perform order of operations on an employees hours (he has gotten two raises since starting after surpassing expectations). The ...
1
vote
0answers
74 views

For every occurence of x insert a new row beneath it

I'm using XPath in Google Spreadsheets. In some of my cells I receive fetched results, in others I get #REF!. The explanation given is Array result was not expanded because it would overwrite ...
1
vote
2answers
63 views

How to insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets? Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say ...
1
vote
1answer
36 views

Combine image and text in Google Spreadsheet

In Google Spreadsheets it is possible to show an image in a cell using the formula =image('http://www.example.com/path/to/image.ext', 3) (where 3 means to use the images' original size). I would like ...
2
votes
1answer
16 views

Calling a date with =concatenate is resolving to strange number

Using: =concatenate("Review Check on ", B2) B2 is "4/20/2015", that I created using the date insert shortcut (command+; on Mac). The final cell resolves to "Review Check on 42114"—the “/”s have been ...
1
vote
2answers
66 views

Google Query For Dates in Last 30 Days

I'm trying to figure out how to do a google query for dates in the last 30 days. I'm using a wordpress plugin that lets you display spreadsheet data and do queries. Here is the current working query ...
3
votes
1answer
50 views

Conditional formatting based on another cell

I use Google Sheets to schedule office appointments. I have set conditional formatting so that if the appointment time slot cells are empty, they are highlighted yellow. (This makes it easier to find ...
1
vote
1answer
23 views

In a google spreadsheet, how can I delete all rows containing given text that could be in any column within the row?

I'd like to be able to delete all rows in a google spreadsheet where a given text string exists anywhere in the row. The text being searched for could be in any cell in the row, and also could be part ...
3
votes
2answers
58 views

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?
1
vote
2answers
24 views

Show number in a cell - based on a value from another cell

I need help with the question on the picture. Can anyone make a formula for me? Thanks
0
votes
0answers
17 views

Google Spreadsheet Time of Edit

I'm currently working on a file with my team. I would need to add a script that returns the following: For a specific column of a specific sheet, when was it edited and by whom into a comment for that ...
3
votes
1answer
26 views

Removing comments completely in Google Spreadsheets

After I've resolved a comment in a Google Spreadsheet, is there a way to delete the whole comment email chain which is currently stored in the comment button on the top right hand side of the ...
1
vote
1answer
139 views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
1
vote
1answer
31 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 ...
1
vote
1answer
29 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?
1
vote
1answer
28 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
36 views

Change an input value in a cell to another value depending on what the input was

What I would like to do is change a number input in the rating column (G3:G9001) to the corresponding number of stars (from 1-10). Howerver I could not figure out how to do this. As a test I tried to ...
1
vote
0answers
45 views

Google Spreadsheet adding new lines in between

I've encountered an issue with Google spreadsheet. When I'm trying to add some text, I'm noticing a new line being added in between. Is anyone else having the similar issue?
0
votes
0answers
34 views

How to make Sheets recognize worksheet tab colors from imported Excel workbook

I have some workbooks I'd like to upload to Sheets. Pretty much everything, from formatting to filters, makes it over reasonably well from Excel to Sheets except for one glaring exception: worksheet ...
1
vote
1answer
36 views

How to count specific items in column?

I just started using Google Spreadsheets. I have a question concerning "counting" a specific word/item in cells. Example: Column A 1. HAT 2. HAT 3. SHOE 4. CANE 5. SHOE 6. HAT 7. - 8. - ...
1
vote
1answer
33 views

Create a function to copy range (values only) to a new tab at specified intervals

Looking to find a function that can copy the values only from a range I am importing from the web at a certain time. Have found a lot of functions that are similar to what I need but can't nail one ...
0
votes
1answer
48 views

Applying formula to lookup value in array of cells in Google Form Responses

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then ...