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.

References
address(row();column()-1)
instead of a direct cell reference likeA2
for the formula inB2
?A1
references, in opposition to$A$1
references, are relative to the cell that contains the formula and works to reference cells by position in many cases. Anyway, my answer includes alternatives to the use ofA1
references.