Google Sheets – Referencing a Range with Variable Name in Another Cell

google sheets

When I would like to import (query) many columns in another sheet I use this code:

=QUERY(sheet2!A1:D1;;)

Now I would like to make this formula a little dynamic making sheet2 variable. So I put the string sheet2 in a cell and make a reference to this cell using indirect(address());

=QUERY(indirect(address(row();column()-1)&"!A1:D1"))

But this did not work.

Example sheet

Please note: I know I could use 

=query(indirect($A$1&"!A1:D1");;)

But I would the reference based on the position of the cell.

Best Answer

Short answer

In the demo spreadsheet B2 cell, instead of address(row();column()-1) use A2 as an alternative replace the whole formula with =QUERY(INDIRECT(OFFSET(B2;0;-1)&"!A1:D1")).

Explanation

address(row();column()-1) returns "$A$2" not "sheet2". The quotes are used to indicate that the result is a string value, not a cell reference.

An alternative is to use OFFSET(B2;0;-1) in the B2 formula. This could be expressed in natural language as get the value of the cell to the left of this cell (the cell that contains the formula).

The final formula is

=QUERY(INDIRECT(OFFSET(B2;0;-1)&"!A1:D1"))

OFFSET()

From OFFSET()

Syntax

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

  • cell_reference - The starting point from which to count the offset rows and columns.

  • offset_rows - The number of rows to shift by.

    • offset_rows must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

  • offset_columns - The number of columns to shift by.

    • offset_columns must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated. height - [ OPTIONAL ] - The height of the range to return starting at the offset target.

  • height is ignored unless OFFSET is used in an array formula. width - [ OPTIONAL ] - The width of the range to return starting at the offset target.

  • width is ignored unless OFFSET is used in an array formula.

Remarks

Instead of QUERY(range_reference) to import a range of cells to another sheet use the array notation:

  • ={sheet2!A1:D1} or
  • ={INDIRECT(OFFSET(B2;0;-1)&"!A1:D1")}

Demo

Example is presented to show that both approach, using QUERY() and using array notation, return the same result.

Note: The image shows comma as the parameter separator due to the default regional settings of the file.

example

References

Related Topic