THE SPREADSHEET KID

In this post, we explore how you can share data betwen Google Sheets, first within tabs in a spreadsheet and then between two or more Google spreadsheets. Accessing the value of a cell on sheet #1 from sheet #2 Sharing data between sheets is very easy and usually consistent whichever software you use:  if you have a sheet called "facts" and you want to insert the value of cell C2 from "facts" into "Sheet2", select a cell in Sheet2, type = and then  =facts!C2 . Most of the time, you won't even need to type anything beyond = as Excel or Google Spreadsheet let you select the cell in the other sheet with your mouse. However knowing the syntax can help you more creative things. syntax :  the name of the sheet ("facts"), an exclamation mark (!) as separator and then the cell address (column+row). Now that you know how to work between between sheets within the same spreadsheet, you are ready to do the same across spreadsheets. Click H...

Importing data from one spreadsheet to another is very similar to importing data between sheets within the same spreadsheet . STEP 1 : Accessing the value of one cell which is in another spreadsheet  Google Spreadsheets allow you to create an unlimited amount of spreadsheets, share and work on them with friends and colleagues in real time. Provided you have view permission to a spreadsheet, you can import a range, a whole sheet from one spreadsheet to another without affecting the source sheet. Addressing is very similar to Step 1, but this time it uses a function called importrange(). Example Here is the  spreadsheet and sheet  we are going to pull data from. Note the value of the key= parameter which is the address of the spreadsheet (do not include the #) : https://docs.google.com/spreadsheet/ccc? key= 0Akd1gJoxWTi1dFFwUDAySGp0VVpYLXM0RHZablVjR3c #gid=0 In a new spreadsheet, we want to import the value of cell C2 from sheet "facts". First, let's create a...

In Part 1 , we saw that Pivot table was the classic spreadsheet way of aggregating and displaying numbers from many rows and columns of data. We also saw that sorting the way we wanted is not always possible. Using the query() function is one way of doing that and it opens up many other possibilities. 2. the Query() function The Query() function can easily fix our sorting problem, but it requires some basic knowledge of SQL, a special language used to extract and order data from databases. Fortunately, the syntax used in Spreadsheets is simplified : The Query() is composed of 3 parameters : =query( source of data ,  the query itself ,  the column headers if required ). Here is an example and its result below : =query(facts!A1:D50,"SELECT D, sum(C) group by D order by sum(C) desc",1) This query gets two columns, Region (column B) and the sum of Population (column C) from the sheet called "facts". It then orders the table by decreasing order of Population...