Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
2
votes
1answer
8k views

How do I sum MM:SS.sss (minutes and seconds) with Google Spreadsheets?

I was looking at a previous post and they used the cell input as follows =ARRAYFORMULA(SUM(TIME(LEFT(B2:B3,2), MID(B2:B3,4,2), RIGHT(B2:B3,2)))) And it works to a point. But the numbers don't roll ...
2
votes
2answers
7k views

Add 1 if there is data in cell

I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected. I want ...
2
votes
1answer
2k views

Can you get hyperlinks to work in a published spreadsheet?

I created a Google Sheets, included a link within it (using the "hyperlink" function) and then published the file to the web. It turns out that when you access it from the web, links don't work. ...
-1
votes
1answer
95 views

Update values on a second spreadsheet and automatically link and update values in the first spreadsheet

I have two spreadsheets: Spreadsheet 1, the initial data is entered manually. I do an IMPORTRANGE into Spreadsheet 2 with order by Col2. This is what I get when I perform IMPORTRANGE (sort on ID): ...
3
votes
1answer
6k views

How do I reference a cell string to use as a note in another cell?

I'm simply looking for how to link one cell's data into another cell as a note1. What I have here simply errors telling me I do not have permission to setNote. :/ function noteSetter() { var ss = ...
0
votes
1answer
125 views

Format a Cell with Script

I need to write a script to see if a cells value is found in an array, and if it is change the background color of that cell. Can't do this with "Conditional Formatting" as it needs to compare with ...
0
votes
1answer
202 views

A function opposed to SUMIF?

I have a huge list of matches between clubs and I want to calculate the goals for and the goals against. I could find a way to calculate the goals for, using: =SUMIF (A35:A46, "Liverpool", B35:B46) +...
3
votes
2answers
1k views

importXML strips leading zeros in Google spreadsheet

I extract one number from an XML online file with Google Spreadsheets. Sometimes the number starts with a zero but the spreadsheet removes it. I'm sure the format of the cell is 'plain text' (I press ...
4
votes
2answers
3k views

Google spreadsheets only shows 6 fonts for me?

Reading around various sites online it seems like you can use any number of Google's web fonts on their Spreadsheets web app, however I only have 6 listed: I've noticed some people have had the ...
4
votes
1answer
7k views

Multiple conditions look up

I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
-1
votes
1answer
210 views

Using "if" (or other function) to recognize a string and sum values

I started programming just a few weeks ago, got a grasp on the concepts and been evolving on it, since I have certain activities with spreadsheets, I decided to take it to the next level and got stuck ...
2
votes
1answer
269 views

SUMIF and FIND combined?

Here are my columns: Column A has values representing screen resolutions such as "1366x768" Column L has a formula to pull the first half of the numbers (the pixel width) out of the text string =...
1
vote
1answer
1k views

SUMIF not working

I have values in column L and I'd like to SUM what is in column B IF the value in L is greater than 1600 Here is my formula: =SUMIF(L2:L,">1600",B2:B) I'm getting a result of 0 in the cell ...
14
votes
4answers
18k views

How do I prepend a string to a column of cells in Google Spreadsheets?

I imagine this is laughably simple but I just don't know what to do. I have a column (A) of cells with strings in them and I want to simply prepend each string in every cell with a web address: A ---...
1
vote
1answer
873 views

Sum if date is in range

I have a table of random dates A and a sum for each date B. The sum is over all the amounts in D, where the date in C is before the date of A. So B3 is D1+D2+D3+D4+D5+D6 because the date in A3 is ...
4
votes
2answers
38k views

Google Spreadsheet; Function to Search a Sheet for String and Return Value of Next Column

Here is what I have so far. it will: Search for keyword in my other sheet (JSON) When it finds the keyword (modname) it returns the row # The keyword is on the A column. What I want is for a ...
1
vote
1answer
425 views

Update Google Drive Chart based on which row selected

I have a table with some pretty basic data (it's a time series going across the columns, and different budget items going down). I want to create a chart that plots a particular row's data against ...
1
vote
1answer
514 views

Google spreadsheets Conditional Formating Formula

I am having trouble highlighting all cells in a column that do not contain the same text that is found in the first cell at the top of the column. I would like to accomplish this with a conditional ...
8
votes
6answers
81k views

How can I use text from a cell as part of a formula?

I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example: I have a cell containing a ...
1
vote
1answer
79 views

Merging spreadsheets [closed]

I have three Google spreadsheets that I want to merge into a master spreadsheet, so that each new entry in any one of the three spreadsheets will automatically update the master. How can I do that ...
1
vote
1answer
6k views

Calculate score and ranking in Google Spreadsheet

I want to create a Google Spreadsheet which will work as a ranking system for a game. One sheet, "Ranking", should display the current ranking of the players, based on their points. Another sheet, "...
1
vote
1answer
3k views

Conditional formatting with if formula

On Google Spreadsheet, I want cell D88 to change background color depending on the value in E88. In my head this should work like this (imagine it's a conditional formatting window): =if(E88="yes") ...
3
votes
2answers
219 views

Altering the Sort Filter in Google Spreadsheets

I live in Australia and when I'm putting in dates, I place the date, followed by month then the year. However when I am filtering/sorting my spreadsheet it's coming up incorrectly. How do I edit ...
0
votes
2answers
1k views

Count word occurrences in survey feedback

I have a suggestions column in a survey feedback, that's being pushed from the survey provider to a Google Spreadsheet. I'm trying to do a bit of wording analysis on it. For example, I'm trying to ...
0
votes
1answer
5k views

In Google Drive spreadsheet, how do I put tabs at the top of the screen?

I want to make a public Google Doc with tabs similar to https://docs.google.com/spreadsheet/pub?key=0AifXEOqTcGcLdFVvWk1GRjVJTHJUaTVLcGViR1RRTFE, but I haven't been able to find instructions or other ...
10
votes
4answers
32k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...
3
votes
1answer
1k views

How many named ranges can a single Google Spreadsheet have?

Is there even a limit to the number of named ranges that you can create within a single Google Spreadsheet? It's rather cumbersome to add a new one, so I would prefer to not just open a spreadsheet ...
0
votes
2answers
1k views

Create chart with total for each score

I have a column within a Google Spreadsheet with values like this: 96 97 96 100 97 97 100 97 95 95 96 I'd like to add a bar chart where each bar represents one of the values and the height of the ...
7
votes
2answers
6k views

Place cell data from Sheets into Docs

I'm trying to populate an invoice. I have a Google Spreadsheets that calculates hours per project, and I want to bring that into a Google Docs. Despite much searching, I have found no way to do this. ...
1
vote
2answers
62 views

Google Sheets, Suddenly decimalised some of my number entries

I've been using Google Sheets to create a record of car parts, entering: each part's serial number, the quantity of that particular part, the name of the part and the location where I'm storing ...
7
votes
1answer
14k views

Data validation from other Google Spreadsheet

My scenario : Google Spreadsheet A Google Spreadsheet B I want a Data Validation in one cell in Spreadsheet A that it's criteria is based from a list from a range on the SpreadSheet B. I know ...
6
votes
1answer
3k views

Stacked area graph of time/duration values

How can I create a stacked area graph of time/duration values? I have a set of times for each date which correspond to each leg of a journey. https://docs.google.com/spreadsheet/ccc?key=...
1
vote
1answer
115 views

Importing columns of data

I'm trying to import columns of data from different sheets. Examples: SEARS -- (sheet one) (I'm entering the prices manually) 1/2013 $8 2/2013 $5 3/2013 $14 Here is sheet two: (I'm entering ...
0
votes
1answer
102 views

Compare two rows of a sheet to another and show characteristics

I'll try to be clear although english is not my first language. I have a first spreadsheet (let's name it PRODUCTS) containing, in different columns, characteristics of different products (Company/S.N....
0
votes
1answer
2k views

Import Range on Google Spreadsheet

I want Cell D1 of this link: https://docs.google.com/spreadsheet/ccc?key=0AupKcedJ0nhPdFFyMVhySWNvNzlYTV9RZ1FmYXdKc0E&usp=sharing#gid=2 To be imported to E9 of this link: https://docs.google.com/...
2
votes
1answer
380 views

How can I personalize HTML emails using Mail Merge and Google Spreadsheets?

I'm attempting to personalize an email that uses HTML coding. Basically, I've made an HTML template that I want to have say the name of a person within the HTML code as well as the subject line of the ...
1
vote
2answers
48 views

Find items that are not duplicates

I have these cells A1 through A7: Mary Susie Jane Mary Susie Elizabeth Mary I want to get the names that do not appear multiple times, so my results should be Jane and Elizabeth. I don not want ...
4
votes
1answer
2k views

Date Formatting for Deadlines

I have set up a Google spreadsheet for managing projects and due dates, and I need to apply the following formatting to a column of dates, but I'm not sure how: Dates more than 7 days from today: ...
4
votes
2answers
3k views

Split text with Google Sheets

I have a Google Sheets with 1,500 cells containing items like: 1,300 users 2236 users, 12 companies 15000 users,153 companies <46,000 users, <200 companies I need to split this content ...
-3
votes
1answer
397 views

How do I conditionally format based on the value of cells in a column?

Unable to create a script. I would like to know if there is one that would allow me to color an entire row based on the value (a different color value) that I will find in the cells of column B. For ...
1
vote
1answer
114 views

Google spreadsheet, sort adds a bunch of rows

I have a function that sorts a table, unfortunately when I call my function it adds a bunch of empty rows at the bottom that I don't like. var kotlijst = SpreadsheetApp.getActiveSpreadsheet() ....
1
vote
3answers
4k views

Make a counter, that counts cell change

I have a cell, let us call it A1, that contains an IF function. Cell A1 changes between two types of text: Healthy Injured What I want is a counter in for example B1 that counts how many times A1 ...
2
votes
1answer
4k views

Google Sheets Print Filtered Rows

I use a Google-Sheet (sheet called "Data") for my payroll. In A1 I paste data from another spreadsheet that I use to store all the data for my business ( example value of job, location, date, ...
1
vote
1answer
7k views

MATCH function does not work

MATCH function does not appear to work at all. Given the following data: $7,109.19 | $867.19 | -$5,374.81 | -$11,616.81 =MATCH(MAX(FILTER(B122:U122, B122:U122 < 0)),B122:U122) returns the error: ...
7
votes
2answers
22k views

How to count items meeting criteria in multiple columns

In Google Spreadsheets I need to count instances of a certain value but only when they match either of two values in another column. So below I would ask for the count of all brown animals that are ...
1
vote
1answer
121 views

Custom Score for Spreadsheet

I have a Google form that has multiple choice answers. I want to score the document (the spreadsheet that is generated) by giving each answer a numerical value (not just right or wrong, but answer A=1 ...
0
votes
1answer
3k views

How to merge multiple rows in Google Docs?

I've got a Google Sheet with columns along the lines of: URL | Pageviews | Time on Page The same URL is sometimes on multiple rows and I'd like to merge the two other columns together so that the ...
5
votes
1answer
25k views

How can I use Google Spreadsheet to format Date in DD/MM/YYYY format?

First thing's first: my spreadsheet's locale is properly set to UK: If I paste in the following date: 30/09/2013 21:15 Then click Format > Number > Date time Nothing changes. I also can't seem ...
5
votes
2answers
10k views

How do I split multiple rows / insert a row below every existing row in Google Spreadsheets?

I use a spreadsheet to keep track of journeys in a lift-club. Each day has its own row, and the number of lifts given to a person will be 0, 1 or 2. I realized that the algorithm for the lift scheme ...
2
votes
2answers
135 views

How do I merge cell data into a new column?

A | B ----- 1 | a 2 | b 3 | c and I'd like to have a third column that merges the data like this: A | B | --------- 1 | 1, a 2 | 2, b 3 | 3, c currently I can kind of do this one row at a time in ...

1
134 135
136
137 138
152