Hot answers tagged

10

Google Docs Upload your existing files. Google Docs accepts most popular file formats, including DOC, XLS, ODT, ODS, RTF, CSV, PPT, etc. So go ahead and upload your existing files. Familiar desktop feel makes editing a breeze. Just click the toolbar buttons to bold, underline, indent, change font or number format, change cell background color ...


4

Google Sheets allows you to protect specific cell ranges so only certain users can edit them.


4

It looks like you want the & operator I'm not quite clear which thing you are concatenating to which in the question; however, you can do either =$BJ$2 & "24" or =$BJ$2 & BH1 or ="Paper" & BH1 assuming you are on row 2 and the number is on row 1 (the $ locks the cell so that it doesn't change when copying/pasting). Here's an ...


3

See if this helps ? =ArrayFormula(query({A1:C,C1:C}, "select Col1, Col2, Col3, count(Col4) where Col1 <> ''group by Col1, Col2, Col3 label count(Col4)''"))


3

I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells. This means you need an absolute reference: H$1 instead of H1. The dollar sign means this number will not be changed as the formula is propagated to other cells.


2

You might look at Docs.com, which is Microsoft's free online service. You won't have to do any kind of conversion with the existing spreadsheet documents.


2

Assuming you have a new line character in every cell and you want to extract everything before the new line, try: =ArrayFormula(iferror(regexextract(A2:A, "^(.+)\n"))) (where A is the column with the addresses and your data starts in row 2 --> change to suit). After that, you can copy the output and use 'paste special' (values only) to overwrite to ...


2

Inserting rows No, spreadsheet functions cannot add or remove rows or columns. The two ways to perform these operations are: manually by the user, or by a script. Alternative solution That said, you can create an output of the desired kind with existing spreadsheet functions, without inserting new rows. My approach uses a helper column that can be ...


2

You can use the fill handle (small square that appears at the bottom right corner of the cell when it is selected/moused over) to fill the formula down.


2

Instead of filter views, I would use the filter command. For example, suppose Sheet1 holds raw data, with A being a character, B the list of "mentioned" chapters, and C the list of "appeared" chapters. You can then add another sheet for "filtered" data, where the user enters chapter number in, e.g., cell A1 and immediately get the list of rows matching that ...


2

Short answer QUERY() allow the use of a subset of SQL but this subset does not include the FROM clause. QUERY() require a single array as the source table, so it could not be used, instead use ARRAYFORMULA(), TRANSPOSE(), JOIN(), SPLIT(), REPT(), FILTER(), COUNTA(), SORT() and the division (/) and concatenate (&) operators to build a "flexible ...


2

Short answer Spreadsheet functions can't insert rows. The alternatives to insert rows are the following methods Manual Scripting Formula building Proposed method and formulas In this answer, a formula building method and a set of formulas are proposed. In order to make it easy to read, some parameters were referred by using named ranges. Each of them ...


1

For the highest value you can use: =INDEX(sort(A:B, 2, false), 1, 1) This first sorts and then retrieves the value from the first row and column of the sorted data. Using the opposite sort order in the formula will yield the lowest value: =INDEX(sort(A:B, 2, true), 1, 1)


1

This is what the command VLOOKUP is for. Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here, A1 is the entry to search for Sheet2!A:C is the range in which the first column (A) is to be searched 3 is the column number from which to take the result (that is, C) FALSE requires exact match, and does not assume the keys are sorted in ...


1

This is a good use case for the SUMIF function. An example is illustrated below:


1

You can use a pivot table to do that. Choose Data > Pivot from the menus, and follow the prompts to set it up. There's an example of something similar here: https://docs.google.com/spreadsheets/d/1VIeA4zAjID0y1jo1FnsbfGA4qmpRgAxMykLXi1pOfhc/edit?usp=sharing


1

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated). ...


1

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet. var range = sh0.getDataRange(); range.sort({column: 2, ascending: false});


1

You can extract the number to another column using REGEXEXTRACT, and then sort the range by that column. For example, suppose this is your C column: +-----------------------+ | 503 in Kitchen & Home | | 85.5 in Bed & Bath | | -263 in debt | | 633.26 in something | | 543 in Outdoors | +-----------------------+ Enter in column D ...


1

One alternative is to use CONCATENATE or the ampersand operator to create the formula for you, but the result will be a text. Then copy, paste as value and edit the result to have a working formula. You could automate this with Google Apps Script. References CONCATENATE - Docs editors Help Extend Google Docs, Sheets, and Forms with Apps Script - Docs ...


1

Another way of doing it, is by using the AVERAGEIF formula. Formula =AVERAGEIF(A1:B1, ">0") Screenshot


1

Here's a function to do thisif a1>0 and b1>0 then average a1 and b1 else add a1 and b1 =if(AND(A1>0,B1>0),AVERAGE(A1,B1),add(A1,B1)) Obviously you'll have to plug in your own cells for a1 and b1 You can find all the google functions documentation here


1

You are using relative reference, which allows the sheet to assume you want to keep the range size the same, just move it. To keep it anchored, you need to use absolute referencing in your formula. This keeps it from moving. =SUM(D$4:D101) Using the $ before the 4 tells it to lock the top of the range to the 4th cell.


1

You can do this using the copyDown add-on by filling in the right formula in line 2. You can find the copyDown add-on here: https://chrome.google.com/webstore/detail/copydown/lipbbdpmkcbplmfnbholpabinnnkbbda


1

Please try, in B2 of Expense_Overview: =sumif(Expense_Detail!D:D,A2,Expense_Detail!C:C) copied down to suit, or: =ArrayFormula(sumif(Expense_Detail!D:D,A2:A,Expense_Detail!C:C)) You have a treat in store whenever you get around to pivot tables. They are very powerful but incredibly easy to use for all that: Select A1:D7 in Expense_Detail, Data, ...


1

You could use the ImportRange function to sync cell data between two spreadsheets. You could then share the individual spreadsheets to your various students. ImportRange documentation: https://support.google.com/docs/answer/3093340?hl=en I've created an example for you, here is your master sheet: ...


1

I'll stick my neck out (yet!) again and say "No" - based on the deduction that if such existed then most of the many questions asking for a date stamp to be applied when a cell changes would not have been posted. However, that may be a "workaround" for you, ie take one of those scripts and apply it.


1

I have been building an online database application over Google App Engine for such use-cases. It is called iFreeTools Creator. Supports multiple data-types for fields including single-line text, multi-line text, numbers, images, geo-location and more. Forms and views are automatically generated for users based on their authorization profiles. Users ...


1

With the new Google Spreadsheet, you are able to add more than 256 columns. To test this, I added this formula in A1: =TRANSPOSE(REPT("A,", 367)) and continued with this formula in A2 =SPLIT(A1, ",") It will raise an error, telling you to add more columns (it will add 300 as a standard): Once added, the split result will expand and show all the data ...


1

So as I was saying... I couldn't find anything that would let me do this that didn't involve installing a full spreadsheet app so I created a simple CSV transposition tool on app engine: CSV Transposer Tool



Only top voted, non community-wiki answers of a minimum length are eligible