Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question

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

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

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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