Google-sheets – In Google Spreadsheets how to make a relative column reference within a query

google sheetsgoogle-sheets-query

I'm working with a large data set surrounding about 200 countries at the moment for a school project. As such, I'm using a query formula to transfer raw data to a computed form to be graphed later. Right now I'm using this query formula (where Raw_data is a named range on a different tab):

=query(Raw_data, "SELECT (A/("&A2&"/10000000)) where (A>0)")

This works fine by itself. However, when dragged to successive columns I manually have to change A to B, etc. twice. If anyone knows how to use a relative column reference it would be greatly appreciated. What I mean by that is if I drag the above query to the right I'd like it to output something along the lines of:

=query(Raw_data, "SELECT (B/("&B2&"/10000000)) where (B>0)")

Best Answer

You want to create a QUERY statement in which the selected column is defined as a relative reference so that it will automatically change as the formula is copied one (or more) columns to the right.

This can be done using Column letters or Column Numbers. The statements look like this:
Letters: =query(Raw_data,"Select ("&A14&"/("&A2&"/10000000)) where ("&A14&">0) ")
Numbers: =query({Raw_data},"Select ("&A5&"/("&A2&"/10000000)) where ("&A5&">0) ")

Refer the screenshot below for references:

Column Letters

  • The select Statement simply references a cell on the sheet ("A14")
  • "A14" contains a formula:
    • =SUBSTITUTE(ADDRESS(row(), Column(), 4),row(),"");
      this returns the Column letter for the cell containing the formula

Column Numbers

  • The range is declared as an array - "{Raw-data}".
  • The select Statement simply references a cell on the sheet ("A5").
  • "A5" contains a formula:
    • ="Col"&column() which equates to "Col1"

In both cases, as the query is copied one column to the right, both the Column Reference ("A14"/"A5"), and the divisor variable ("A2") change and become "B14"/B5" and "B2" respectively.

Note that the Column variable is repeated in the "where" clause.

Screenshot

Screenshot

Related Topic