Google Spreadsheet ImportXML Auto Refresh Cell Based on Drop Down Selection

Question:

( by JeremiahL )

Hello,

I'm having a bit of difficulty figuring out how to make my spreadsheet respond correctly. I have a range of cells which draw their value from an outside game API. Part of the script for the api allows the in-game region to be selected using a system ID. I've created a drop-down list with the system ID's I want to use and then the code for the dynamically loaded cells calls the cell which has the drop down list. The problem I'm encountering is the drop-down list doesn't refresh the values. When I first open the document, the initial cell value (first item on the list) is used in the script and changing it doesn't do anything. Can anyone explain how to cause changing the drop-down value to also refresh all of the cells that call data from that list?

The dropdown list is C2 and the code for calling the value is currently in J13. Eventually the code from J13 will be moved to all of it's proper spots on the spreadsheet, but I'm just working on that cell right now until I get it figured out. 

Thanks to anyone who can help.

Solution:

Have a look at the following screenshot:

And I have the following formula in Cell J13:

=importXML("http://api.eve-central.com/api/marketstat?usesystem="&$C2&"&typeid="&join("&typeid=";$A4:$A5);"//sell/min")

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 Count instances in an array with conditions

Question:

( by amj05 )

If I have two columns that look like this:

A	X, Y
B	Y
C	X, Z
B	X, Y, Z
D	Z
A	Y, Z
A	X, Y, Z

Is there a formula to count the total number of times Y appears to the right of A (whether or not Y appears with anything else)? The only way I know involves way too many lines (especially since there will be more variables than in this example). Thank you!

Solution:

Have a look at the following screenshot:

I have the data in Column A and B.

And I have the following formula in Cell D1:

=query(A:B;"select A,count(B) where B contains 'Y' group by A label count(B) '' ";0)

And if you want to only count the number of times Y appears to the right of A, then I have the following formula in Cell G1:

=counta(iferror(query(A:B;"select A where A = 'A' and B contains 'Y' ";0)))

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 Kate Dyer )

I am trying to create a chart with the following format of data:

On one tab, I have a list of items, eg. Chocolate, Strawberry, Vanilla

On another tab, I have a list of names in one column and their flavour preference in the next column, selected from a drop-down that uses validated data from the first tab (i.e. they select Choc/Straw/Vanilla from a drop-down)

I want to have a pie chart populate with how many people chose each flavour. Is this do-able?

[Using Sheets on Mac OS X in Chrome]

Solution:

Have a look at the following screenshot:

I have the data in Column A and B.

And I have the following formula in Cell D1:

=query(A:B;"select B,count(A) where B<>'' group by B label count(A) '' ";0)

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 Countif with Arrayformula

Question:

( by Adrian Kabigting )

Not sure on how to approach this:

So here's how my first spreadsheet is setup

Column A - staff name

Column B - certification boolean (Y or N)

Now in a different worksheet, I am distributing the staff into 2 different locations.  I'm simply just typing their names under a table with the location name as the header, i.e. A1="Location 1", B1="Location 2", A2="Joe"...

Now as I type names under Location 1, is there a formula to count how many people have the certification identified by the boolean value in the previous worksheet, column B?  Sort of like "count the names if they have the certification"

I understand COUNTIF (I think at least) but I don't know how to apply it to a range, checking if each cell value is contained in Column A of the first spreadsheet, and then checking if Column B contains "Y" and then counting them.

I hope this makes sense, any help would be appreciated!

Solution:

Have a look at the following screenshot:

Sheet1:

Sheet2:

I have the following formula in Cell B2:

=arrayformula(if(A2:A="";"";countif(if('Sheet1'!B2:B="Y";'Sheet1'!A2:A;"");A2:A)))

Also have a look at the following alternate way of getting the solution:

Sheet3:

I have the following formula in Cell A2:

=query('Sheet1'!A:B;"select A,count(B) where B='Y' group by A label count(B) '' ")

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 Find most recent date in a column

Question:

( by hendis )

I'm using Google Spreadsheets on a HP laptop running Win7 through IE10. (Also have Chrome for Win 7 installed, too.)

My situation is this: 

I have a spreadsheet that lists dates sequentially in Column A from earliest to latest (starting in Cell A6). In Column B, I list clients' names opposite the date that I worked there. 

For example, cell A13 may show 9/17/11, while cell B13 might show "Arder Co." 

However, "Arder Co." will appear in Column B many times. What I want to do is find the latest date in Column A corresponding to a specific client's name.

For example, if "Arder Co." appears in cells B13, B37, B48, B53, and B66, I want to return the most recent date, which will appear opposite to cell B66, or in cell A66.

This is easier to explain than to do. Thanks in advance for any ideas or help.

Solution:

Have a look at the following screenshot:

I have the following formula in Cell D5:

=query(A5:B;"select B,max(A) where B<>'' group by B label max(A) 'Date' ";1)

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 Create a self-updating Labor Calculator for small business

Question:

( by Xander Legg )

Hi there, I'm the General Manager of a small restaurant.  The owners and I have been trying to work in incentive based pay (such as food/labor) in addition to their hourly wages.  We want to enforce this by creating a spreadsheet that will show the labor percentage of the last 7 running days, and give them a visual aid as to how they're doing, and how close they are to hitting bonuses.  We already have a well-organized spreadsheet with Sales, Labor Dolars, etc. saved, but to keep those sheets less accessible and manipulable I'd like to create a separate spreadsheet that uses the IMPORTRANGE function. I guess my main question is, how can I show labor (Sales/Labor $) for the past 7 days in a consistent place that updates as we update the sales and labor records?

Spreadsheet:

Solution:

Have a look at the following screenshot:

I have the following formula in Cell B2:

=sum(query(C2:E;"select D order by C desc limit 7";0))

I have the following formula in Cell B3:

=sum(query(C2:E;"select E order by C desc limit 7";0))

I have the following formula in Cell G1:

=query(C1:E;"select * order by C desc limit 7";1)

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 pair data from two separate data ranges

Question:

( by AlexSFr )

Hello,

Ok, so I have a spreadsheet where employees are making transactions against a requistion number. One sheet keeps tract of the individual transactions which is entered either manually or via a form. Another sheet shows the current requisition which the employee is assigned to.

What I would like to do is pair the currently assigned requsition number of the employee to every transaction the employee made in the first sheet. Example:

Here is also an example spreadsheet:

If this could be done with a query where the combined data is on a third sheet, that would be fine as well.

Thanks in advance.

Solution:

Have a look at the following screenshot:

I have the following formula in Cell E2:

=arrayformula(if(C2:C="";"";vlookup(C2:C;'Sheet2'!A:B;{2}*sign(row(C2:C));false)))

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