1

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)")
Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
4
  • Thank you so much! So I tried both solutions you provided and I couldn't get the Column letter one to function quite right. However, the one by column number works perfectly once I added in a tab reference because my Raw_data is in another tab. My equation looks like this: =query({Raw_data}, "SELECT ("&'tab1'!B1&"/("&B2&"/10000000)) where ("&'tab1'!B1&">0)")
    – Jack Morgenstein
    Commented Mar 29, 2020 at 18:42
  • Excellent; Sorry that the "Letters" formula isn't working. what error value does it return, and what is the error message. FWIW, this is my equivalent "Column Letter" formula to go with your "Column Number" formula: =query(Raw_data,"Select ("&'tab1'!B14&"/("&B2&"/10000000)) where ("&'tab1'!B14&">0) "). I'd want to check that you DO have a cell(s) that contain the letters "A" & "B" (or whatever letter it is that you need); also check the length of the letter value (=len(B14), or whatever) to make sure that the length = 1; and the range for Column Letters is not in curly brackets.
    – Tedinoz
    Commented Mar 30, 2020 at 0:48
  • So I had originally tried both the letters and column reference methods without the additional tab reference 'tab1'! and without it neither worked and both output the error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Israel. (or whatever country was in the specific column). I was trying different things to try to make it work and added the tab reference on the column version and it started working and that's when I sent my response. I just went in and tried the letter version with the tab reference and it works now too, fortunately. Thank you so much for your help!
    – Jack Morgenstein
    Commented Mar 30, 2020 at 16:10
  • Glad that is resolved
    – Tedinoz
    Commented Mar 30, 2020 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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