Refer to cell on different sheet plus 2 rows

[Jimbola]

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.

 

[MrKowz]

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]

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]

Sorry my bad I meant;

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.

 

[MrKowz]

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]

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)

 

[MrKowz]

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]

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]

Sorry my bad I meant;

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]

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))