How can you write queries in a Google spreadsheet so that the columns are kept up to date?

Question

Using Google Docs/Drive 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")

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 showed.

2 - Assemble the query string like this:

=QUERY( B:J, "SELECT AVG(" & INDEX(COLS, COLUMN(J1)) & ") GROUP BY " & INDEX(COLS, COLUMN(G1)) )

Hope it helps.