9

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes.

Is there a way to write these queries so they don't need to be updated every time a column is added or removed?

Is it possible to use named ranges in queries to solve this problem?

Here's an example: If you add a column after 'F', then column 'G' gets pushed to 'H' and the meaning of the formula changes.

=Query(B:J,"select avg(J) group by G")

Related questions

This question is not the same as Using Query with column headers instead of column letters because this one is focused on the use of named ranges.

migrated from superuser.com Oct 29 '12 at 5:11

This question came from our site for computer enthusiasts and power users.

5

It's a kind of tricky, but it is possible with a helper Range and some concatenation.

What needs to be done:

  1. Create a named range, COLS, to carry the column letters like this:

    A  
    B  
    C  
    D  
    E  
    ...
    

    Do it in a vertical way as shown.

  2. Assemble the query string like this:

    =QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " & INDEX(**COLS**, COLUMN(G1)) )
    
  • 1
    The named range will work both in vertical or horizontal shape. – Rubén Aug 11 '16 at 0:18
  • @Rubén is probably right. – Oneide Nov 15 '16 at 0:07
3
+100

Is there a way to write these queries so they don't need to be updated every time a column is added or removed?

Is it possible to use named ranges in queries to solve this problem?

Yes, you could use named ranges instead of built-in column headers but the basic idea is the same as other answers to this an other similar questions in this site suggested: the spreadsheet should be set to build a dynamic SQL string.

Let say that J and G corresponds to named ranges Grades and Class respectively.


The following approach works only when the columns are between the columns A and Z


CELL("address",Grades) will return "$J:$J". To get the column reference use MID(CELL("address",Grades),2,1) to get "J". The final formula is

=QUERY(B:J,"SELECT AVG("&
MID(CELL("address",Grades),2,1)&
") GROUP BY "&
MID(CELL("address",Class),2,1))


The following approach works for all cases


COLUMN(Grades) will return an integer (a number of the form 1,2,3 etc.) To simplify the formula to create the SQL statement argument I suggest to force SQL to use column references of the form COL1, COL2, etc. by using the array notation ({B:J}). The final formula is

=QUERY({B:J},"SELECT AVG(Col"&COLUMN(Grades)-1&") GROUP BY Col"&COLUMN(Class)-1)

Demo Spreadsheet

References

Using arrays in Google Sheets - Google Docs editors Help

1

Ruben's answer doesn't solve the problem for all cases because MID(CELL("address",Grades),2,1) fails if the Grades range is moved to a double-letter column like $AA:$AA.

In that case, the formula MID(CELL("address",Grades),2,2) is needed. For named ranges that span or abut the $Z:$AA range, this formula becomes problematic.

The formula REGEXEXTRACT( CELL("address", Grades), "\$([a-zA-Z]+)\$") solves the problem for the general case, but I strongly question its CPU efficiency when used all over a large spreadsheet.

  • In my answer there are two approachs but this answer only critizes one of them. The second approach should work for all cases. – Rubén Aug 17 '16 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.