Dynamic Sheet Name in Query in Google Spreashsheet

4
votes

In Google spreadsheet I want to query data in another sheet but the problem is that the name of sheet is present in a cell. So is there a way in QUERY function to dynamically mention sheet name. Basically I am trying to do something like this but with dynamic sheet name:

=QUERY('2012'!A2:F;"select C, sum(F) where A='December' group by C order by sum(F) desc")

I tried to do this but I get Parse Error:

=QUERY(INDIRECT("Overview!L5")!A2:F;"select C, sum(F) where A='December' group by C order by sum(F) desc")

In which Overview!L5 is the cell with sheet name to query. I also tried to concatenate quotes around INDIRECT but that didnt work either.

I think it is quite evident what I am trying to do from the query i.e. get sum of values in cells grouped by values in other cells.

2
google-apigoogle-sheetsgoogle-spreadsheet-api

2 Answers

14
votes

the INDIRECT looks to be the problem. Try like this:

=query(INDIRECT(A1&"!A5:A10"),"select Col1")

i.e. if Cell A1 contains "food" the above is the same as:

=query(food!A5:A10,"select A")

and the same as:

=query(INDIRECT("food!A5:A10"),"select *")

**Note: the indirect uses "Col1" etc and not "A" because it does not pass the col letters.

Also ... The google groups forum might be a good place to look for spreadsheet formula answers. productforums.google.com/forum/#!categories/docs/spreadsheets

0
votes

Easiest way to use dynamic structures in a query is to not include functions inside query but prepare strings in separate cells, for instance A1 for address B1 for arguments and then just QUERY(A1;B1)