Refer to cell on different sheet plus 2 rows

Jimbola

Board Regular
Joined
Jun 23, 2010
Messages
60
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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)
 
Upvote 0
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:
Upvote 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.
 
Upvote 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.



OK

Sheet2A1 contains 1
Sheet2A2 contains 2

Sheet1A1 = 'Sheet2'!A1 returns 1
Sheet1B2 = formula that returns 2 by referencing Sheet1A1
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top