Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

I was also attempting to solve this issue and came up with a short script that allows you to write queries like this:

You use it like this:

=QUERY(A9:C13, QueryByName(A9:C9, "SELECT `Name`, `country` WHERE `age`>7"))

You can easily replace the two ranges above with named ranges if desired, and if a new column is added to the data set at a later point, you shouldn't need to update the query. 🎉

I posted the code and more detailed instructions in another thread, see here.