5

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.

  • Why do you want to use something like address(row();column()-1) instead of a direct cell reference like A2 for the formula in B2? – Rubén Jan 15 '16 at 13:32
  • @Ruben Because I need to reference to the cell by position and not by simple reference. – Francesco Jan 15 '16 at 16:26
  • 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 of A1 references. – Rubén Jan 15 '16 at 16:33
4

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

  • Thank you Rubén. But I need reference the cell by the position relative to the cell in which i write the formula. So I cannot use B2 as in ={INDIRECT(OFFSET(B2;0;-1)&"!A1:D1")}. I need to use ={INDIRECT(ADDRESS(same row;one column left)&"!A1:D1")} or other formula. – Francesco Jan 15 '16 at 16:43
  • @Francesco: I updated the answer. Hopefully it's now more clear. – Rubén Jan 15 '16 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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