Google Sheets – How to Prevent Formula References from Changing When Copied

formulasgoogle sheets

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.

Best Answer

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 

sheet2!A1:K

you need to write 

sheet2!A$1K

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

sheet2!A:K
Related Topic