# Refer to cell on different sheet plus 2 rows

#### Jimbola

##### Board Regular
Hello I have this formula pointing to a cell on a different sheet;

A1
='Sheet2'!A1

In A2 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row. I thought I could use OFFSET with INDIRECT but was unable to make it work.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### MrKowz

##### Well-known Member
As long as you don't have your cell reference set as absolute, you should be able to just copy the formula down the column, and it will iterate the referenced row.

#### Swapnil Shah

##### New Member
You can just copy A1 Cell and paste it in to A2 This will copy it to A2 cell with reference to Sheet2!A2

#### Jimbola

##### Board Regular

In B1 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row. I thought I could use OFFSET with INDIRECT but was unable to make it work.

But in different cells I could be referring to +x rows or +x columns, so a method would be useful.

Last edited:

#### MrKowz

##### Well-known Member
To make sure I understand, you want A1 to refer to Sheet2!A1; B1 to refer to Sheet2!A2; C1 to refer to Sheet2!A3?

If so, try placing the following formula in A1 and copy to the right:

=OFFSET(Sheet2!\$A\$1,COLUMN()-1,0)

#### Jimbola

##### Board Regular
To make sure I understand, you want A1 to refer to Sheet2!A1; B1 to refer to Sheet2!A2; C1 to refer to Sheet2!A3?

If so, try placing the following formula in A1 and copy to the right:

=OFFSET(Sheet2!\$A\$1,COLUMN()-1,0)
No that's not it.

I have sheets2 - sheet10, sheet1A1-A10 points to A1 in each sheet, then in sheet1B1-B10 I need a formula that will use sheet1AX+C/+R to pull back info from the respective sheets. I can then drag this formula down and it will work for all the sheets.

Something like;
=OFFSET(INDIRECT(A1),1,0)

Last edited:

#### MrKowz

##### Well-known Member
I am very confused... can you please provide some sample data and what your desired result is? This is the first you've mentioned sheets beyond sheet 2, and now you're talking about in different cells you'd be moving by a different number of rows/columns.

#### Jimbola

##### Board Regular
I am very confused... can you please provide some sample data and what your desired result is? This is the first you've mentioned sheets beyond sheet 2, and now you're talking about in different cells you'd be moving by a different number of rows/columns.

OK

Sheet2A1 contains 1
Sheet2A2 contains 2

Sheet1A1 = 'Sheet2'!A1 returns 1
Sheet1B2 = formula that returns 2 by referencing Sheet1A1

#### MrKowz

##### Well-known Member

In B1 I want the formula to point to 'Sheet2'!A2
Here you say you want B1 to refer to Sheet2!A2

Sheet2A1 contains 1
Sheet2A2 contains 2

Sheet1A1 = 'Sheet2'!A1 returns 1
Sheet1B2 = formula that returns 2 by referencing Sheet1A1
Here you say you want B2 to refer to Sheet2!A2

Can you see my confusion? I don't understand your requirement, as it is changing every post.

#### Rick Rothstein

##### MrExcel MVP
In B1 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row.
Give this formula a try (place it in cell A1... yes, cell A1... and copy it across):

=INDEX(Sheet2!\$A:\$A,COLUMNS(\$A:A))

Last edited: