I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here.

The =QUERY statement I'm trying to use is this:

=QUERY(sheet2!A1:K; "Select B, C where A =" & B1  ; 0)

It works if I remove the cell reference from where A =" & B1 ; 0) and I replace it with a static value like where A contains '123456' ").

Any suggestions on how to get the =QUERY to work using a cell=referenced value? The heading for the column being referenced is TEXT but the value of all rows below the heading is a NUMBER.

1 Answer 1


The problem you have is that the data row reference changes when you drag-copy the formula down. It needs to be made static. So instead of


you need to write


or in this case (as you're starting from row 1 anyway), you can use:

  • Tom, thank you. Your change helped. The problem I have now is my spreadsheet is populated by a Google Form and the =QUERY doesn't auto fill down. Any suggestions?
    – Mr. B
    Nov 12, 2013 at 17:57

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.