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
  • I dont think this will work for named ranges that are on on different sheets. Wondering if +2 years since, if there are better solutions to this...such as simply being able to reference the darn named ranges in the query itself – mike01010 Oct 15 at 3:46
0

I came across this page at infoinspired.com that explains one way of doing it. I tested it exactly as in the example he gave and it worked. The setup:

      A        B        F                                     G
 1  names     Age   =left(address(row(B1),column(B1),4))   =QUERY(A:B,"Select "&Age)
 2  joey      32
 3  nicole    26
 4  sally     66

His explanation of F1: The ADDRESS formula which includes the ROW and COLUMN functions returns the cell address of B1. The LEFT function extracts the first letter that is the column heading from it.

--- Click on cell F1 and then go to the menu Data > Named ranges and name the range "Age" (without quotation marks)

Now you can formulate your query as: =QUERY(A:B,"Select "&Age)

He's got more complicated examples in his page that I'm about to test now, but I'm encouraged that the first example worked!

Edit - sorry if this is exactly what others have been proposing with their answers. I just found this example crystal clear and was able to figure it out. Thought it might help others with the same question.

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.