# Referencing a range in a sheet with variable name in another cell

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

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

• 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