Google Spreadsheet Complex Query Formula

Question:

Hi G Community,

I was wondering how to create a formula to subtract one cell from the first populated cell above it in the same column, and put the results in the first cell below it. I attached a print screen of my example.

So as you can see the initial balance of Food is $100 (B2).

- On 6/2/13, I bought $25 (B7) of food. So the first populated cell above this cell is B2 with $100. So I need a formula in B8 that will subtract whatever I put in B7 from the first populated cell above it, which in this case it's B2.

- On 6/13/13, I bought $10 (cell B11) of food. So in this case, the first populated cell above B11 is B8.

So the first populated cell above a cell will always vary. Can anyone help me on how to create a formula like that?

Thanks in advance.

~Diana

Solution:

Put the following formula in Cell B8:

=query(arrayformula(indirect("B" & (row()-2) & ":B1"));"select Col1 where Col1>0 limit 1 offset " & (rows(query(arrayformula(indirect("B" & (row()-2) & ":B1"));"select Col1 where Col1>0";0))-1);0)-B7

Have a look at the screenshot below:

And now whenever you want to have the formula, you can just copy (ctrl C) from Cell B8 and then paste it (ctrl V)

For example paste it in Cell B12, so you will get the result $65 there. 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Query For Displaying Pie Chart

Question:

(by Matt)

Morning All,

I've been looking at this for the last hour and have managed to get myself quite confused.  I have a Spreadsheet that looks like this (example only):

This is data collected as a result of a Google Form Survey.  In it we asked users from the business "Have you ever downloaded apps from Google Play?".  The second question is "Did you find anything useful for work purposes?".  After sending out the questionnaire I realised that some users are answering 'No' to the first [required] question and then answering 'No' to the second [optional] question, which is affecting the results slightly

What I would like to do is create a pie chart which displays - where users have said 'Yes' they have downloaded apps from the apps store, how many of them found an app useful for business. Something, perhaps, along the lines of a field which contains COUNT 'Yes' in column B where column A also has a 'Yes'.  Also, COUNT 'No' in column B where column A also has a 'Yes'?

The responses to the survey are still coming in so I would like this to be dynamic.  I started creating a new sheet and tried to use Array formula to create a table form which to derive the pie chart, but have ended up getting very confused.

Can anyone point me in the right direction?

Thanks

Matt

Solution:

Make a new sheet, and in this sheet put the following formula in Cell A1:

=query('Sheet1'!A:B;"select B,count(A) where A='Yes' group by B order by B desc label B '',count(A) ''";0)

I am assuming that your first sheet's name is 'Sheet1', or else replace 'Sheet1' with your sheet's name.

Now you will get the result as you can see in the screenshot below:

And now you can insert pie chart, and thus you can show the data in form of a chart.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Advanced Query

Question:

(by Daniel)

Good day

I'm working in this spreadsheet 

Screenshot of Sheet 'data':

Screenshot of Sheet 'Lookup':

In C2 want to put a function were It should look up first for A2 in =Lookup!A1:K1

Like using HLOOKUP depending what is it it will use then VLOOKUP lets say of B2 and prompt me the value that matches in the sheet LOOKUP next to the value that it should be

Pretty much Like using

First HLOOKUP to find which column to use then VLOOKUP to check which of the rows to use and then to display the value next to it

In this case, Social network: Instagram and Type: followers, so it would display me "j"

quite mixed up with this to make it simple, rather than do IF's and HLOOKUP's
thank you.

Solution:

Put the following formula in Cell C2 of Sheet 'data':

=query(arrayformula('Lookup'!A:Z);"select Col" & MATCH(A2;'Lookup'!A1:1;0)+1 & " where Col" & MATCH(A2;'Lookup'!A1:1;0) & "='"& B2 &"'")

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Navigate to another Sheet

Question:

Hello. I am trying to link cells in in one Google spreadsheet to cells in another, related sheet. Which is to say, I would like to not only pull data from sheet 1 to populate sheet 2, ideally, I would be able to click on the data in sheet 1 and it would take me to that cell in sheet two. Is this possible?


Solution:

As of now (June 2013), you can't directly set any cell to navigate you to another sheet. But I will show you the work around of doing so.

First of all insert an image, which can be of any size, for this example I have made an image of small size which looks like a button. To insert image, go to "Insert" Menu then select "Image..."

Have a look at the screenshot below:

Now, to move this button anywhere, just right click on it and drag it to your desired position. And also now you have to assign script to it, for that again right click on it and the select "Assign script...".

After that you have to give the function name. You have to write same function name in your script editor.

I have named it "myFunction". And now you have to go to script editor and make a new script. Delete all the existing code in the script file and insert the following code:

function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  ss.setActiveSheet(sheet).setActiveSelection("D5");

}

In above code, you can see I have used to navigate to Cell D5 of "Sheet2", which you can change as per your requirement. But keep the function name same as that you have inserted in the "Image" >> "Assign script.."

That's it, now you are almost done. For the first time run the script function "myFunction" from script editor. And then you can click on the image file, and by clicking on it you can navigate to "Sheet2" Cell D5.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Group By Query

Question:

(by Serg Valero)

Hello Everyone,

Hope I can explain myself, I have the following table

Client Course Date

1         A         1/10/2013

3         B         8/28/2011

3         B         10/15/2012

3         A         10/2/2013

1         A         4/8/2013

1         B         5/10/2013

I need to have the minimum date of each course by client for having a table like this:

Client Course Date

1         A         1/10/2013

1         B         5/10/2013

3         A         10/2/2013

3         B         8/28/2011

In excel I achieved it with =min(if( )), is there a way of doing it in spreadsheets? 

Thnx!

Solution:

Have at the screenshot below:

I have the table data in Columns A,B and C.

I have the following formula in Cell E1:

=query(A:C;"select A,B,min(C) where B<>'' group by A,B label min(C) 'Date'")

And if you want to have the result in another sheet then put the following formula in another sheet:

=query('Sheet1'!A:C;"select A,B,min(C) where B<>'' group by A,B label min(C) 'Date'")

In above formula replace Sheet1 with your Sheet Name.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Group By Query

Question:

I have a spread sheet that has multiple entries for each user

user       *        1

JAY                 1

JOE       *         1

JOHN               1

JAY                  

JOHN     *         1

JOE       *          

STEVE  *         

Now I have two queries, one that counts the number of * in column 2 grouped by user and one that counts the number of 1's in column 3 grouped by user.  Is there a way to put both counts in the same query so you can build a table like:

user         *        1

JAY         0        1

JOE         2        1

JOHN       1        2

STEVE     1        0

If I try with the two queries I get:

JOE      2  JAY     1

JOHN   1   JOE     1

STEVE 1   JOHN   2

And that does not look good

Solution:

Have a look at the screenshot below:

I have entered the data in Column A, B and C.

And then I have inserted the following formulas:
In Cell E1:
=query(A2:C;"select A,count(B),count(C) where A<>'' group by A label A'User',count(B) '*', count(C) '1'")

In Cell E7:
=query(arrayformula(if({1,0,0};A2:A;if({0,1,0};if(B2:B="*";1;"");C2:C)));"select Col1,count(Col2),count(Col3) where Col1<>'' group by Col1 label Col1'User',count(Col2) '*', count(Col3) '1'")

Formula in Cell E1 was not counting stars that is * , so I coded a new formula which is in Cell E7.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet Calculating Week Number

Question:

Hi 

Please help me with a formula if you can, i will be thankful to you.

To refer the sheet click this link:

'Column A' has date's and 'Column B' has ReasonI want to capture count of each reason between a week, week starts on Mon and ends Sunday. Example Mon, 17 Jun'2013 - Sun, 23 Jun'2013.

I want formula to be used in Sheet 2 but data remains in Sheet 1

Let me know if you have any queries.

Solution:

Put the following formula in any Cell in Sheet2:

=query(arrayformula(if('Sheet1'!B3:B="";"";if({1,1,0};'Sheet1'!A3:B;left(text('Sheet1'!A3:A;"'Week' w yy");7))));"select Col3,Col2,count(Col1) where Col3<>'' group by Col3,Col2 label Col3 '',Col2 '',count(Col1) ''")

Have a look at the screenshot of Sheet2 below:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet How to copy values to a different spreadsheet

Question:

how to copy values to a different spreadsheet using apps script

Browser: Google Chrome

OS: Mac Os 10.8.4

Hi! I am trying to create a script that copies a sheet to different spreadsheet file. I manually do this by selecting the range, then paste special, then values only, finally I click on the sheet's tab and select 'copy to' and choose the destination. I have been trying to do the same operation through apps script but can't get the values, I only get the format and formulas.

function copiaConsolidado()

{

  var hoja = SpreadsheetApp.getActiveSheet();

  var destino = SpreadsheetApp.openById('id');

  hoja.copyTo(destino);

}


Solution:

Try the following script:

function copySheetValues()
{
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sourceDataRange = source.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();
  
  var destination = SpreadsheetApp.openById('0AmMTqpzD9YRndFh5aWk0UUhmejJMMXZWYUZkSDRsY0E');
  destination.insertSheet(sourcename, 0);
  destination.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);

}


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive