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)
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.
Sorry my bad I meant;
In B1 I want the formula to point to 'Sheet2'!A2
Sheet2A1 contains 1
Sheet2A2 contains 2
Sheet1A1 = 'Sheet2'!A1 returns 1
Sheet1B2 = formula that returns 2 by referencing Sheet1A1
Give this formula a try (place it in cell A1... yes, cell A1... and copy it across):In B1 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row.