Tagged Questions

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

learn more… | top users | synonyms (1)

6
votes
2answers
404 views

Google Sheets: “Split” delimited string, preserving blank entries

I am trying to use the SPLIT and JOIN functions in Google Sheets (the new version) to manipulate some data, and I am running into problems because SPLIT is not preserving blank entries. The data ...
1
vote
0answers
83 views

Way to automate collection of goo.gl data into Google Spreadsheet?

Is there a way to automatically collect analytics data from goo.gl into Google Spreadsheets or Excel? Specifically I am looking to automatically bring the total clicks from my links into a spreadsheet ...
1
vote
1answer
115 views

Make dynamic array blank based on adjacent values in Google Spreadsheets

I'm attempting to indicate certain months that fall between the end of one period and the beginning of another. I have the months of the year in column C: values 1 through 12. It's a running snapshot ...
1
vote
2answers
49 views

How do I cancel editing a cell, in Google Spreadsheets?

I sometimes double-click on a cell to see the formula or whatever, or I just accidentally start typing in the cell. How do I cancel the edits? Esc and Ctrl+Z don't work, so I typically hit Enter and ...
3
votes
1answer
496 views

Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
2
votes
2answers
287 views

Google Spreadsheet, put data in a row based on whether a cell has an X in it

I have a list of tutors in a Google Spreadsheet: Sheet 1 Name | Address | Phone | Profile | Subject1 | Subject2 | Subject3 | ... | SubjectN I would like to populate additional sheets with the ...
1
vote
1answer
127 views

Return text from multiple cells on another sheet with linefeed if dates match

I have an example here of my spreadsheet. Basically my main page has a drop-down (via data validation) where you can toggle through any month and it responds by showing data for that month. What I ...
3
votes
2answers
613 views

Upgrade existing Google spreadsheet to New Google Sheet

I keep getting the message on my Google spreadsheet that I'm using the old spreadsheet. There is an option to create the new Google sheet, but no option to upgrade the existing. Please check this ...
2
votes
1answer
1k views

Date and time stamp automatically added in column

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
0
votes
0answers
54 views

Import info from multi tabs in a spread sheet to one sheet

I'm looking to move data from "sub sheets" to the main sheet. I know I've done this before but it's been 5 years since. So here's what I'm looking for: ...
2
votes
2answers
453 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 ...
1
vote
2answers
159 views

Google Spreadsheets: 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
324 views

Can you get hyperlinks to work in a published spreadsheet?

I created a Google spreadsheet, 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. ...
0
votes
0answers
74 views

Google Docs update values on a 2nd spreadsheet and automatically links and updates values in the 1st spreadsheet

I have 2 spreadsheets. Spreadsheet 1, the initial data is entered manually. I do an importrange into spreadsheet 2 with a order by Col2. This is what I get when I perform importrange (sort on ID) ...
0
votes
1answer
340 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 note. What I have here simply errors telling me I do not have permission to setNote. :/ function noteSetter() { var ss = ...
1
vote
1answer
65 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
58 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
221 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 ...
3
votes
2answers
748 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 ...
2
votes
1answer
100 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
121 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
71 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
57 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 ...
0
votes
0answers
109 views

Pass range in Google Apps script custom color sum function without using A1 notation

I want to sum up lines with Google Spreadsheets where the cells have a certain color. Since it needs to be copied down I want to have a function that doesn't use the A1 notation. There is already a ...
3
votes
3answers
245 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
190 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 ...
1
vote
2answers
921 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
151 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 ...
0
votes
0answers
81 views

Merging two lists with common entries in Google Spreadsheet

We're populating two separate worksheets in a Google Spreadsheet, through 3rd party integration. In essence, we're collecting customer information through a Wufoo form and their payment information ...
0
votes
1answer
166 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 ...
1
vote
2answers
514 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 ...
0
votes
1answer
53 views

Merging spreadsheets

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
404 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
883 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): ...
2
votes
2answers
86 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
83 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
0answers
58 views

Why does my Spreadsheet keep automatically adding rows?

It probably has something to do with my Array formulas. I'm guessing but why does it seem like whenever I input a value in one of the column's cells, it would keep adding rows automatically? It also ...
0
votes
0answers
38 views

Count occurrence of names separated by quarter

I'm building a quarterly count sheet from a log sheet ('2014 Sanction Log') in the same spreadsheet where L:L is where the names exist that I want to count the occurrence of and A2 is the name on the ...
0
votes
1answer
162 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 ...
0
votes
0answers
62 views

How do I sort forms individually based on individual events in Google Forms?

My purpose is to conduct 5 events, namely 1,2,3,4,5 say ... I'm using a Google form to accept responses from people. Emails are sent to people automatically and they can register for more than 1 ...
1
vote
1answer
1k 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, ...
1
vote
1answer
51 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 ...
1
vote
2answers
166 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 ...
2
votes
1answer
326 views

Place cell data from a Google Spreadsheet into a Google Document

I'm trying to populate an invoice. I have a Google Spreadsheet that calculates hours per project, and I want to bring that into a Google Document, but despite much searching, have found no way to do ...
1
vote
2answers
46 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 ...
0
votes
0answers
75 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
281 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. ...
0
votes
1answer
68 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
31 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 ...
0
votes
1answer
984 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: ...