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.
 

Some videos you may like

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
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
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
Joined
Aug 30, 2017
Messages
44
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
Joined
Jun 23, 2010
Messages
60
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:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
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
Joined
Jun 23, 2010
Messages
60
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
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
Joined
Jun 23, 2010
Messages
60
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,746
Messages
5,446,253
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top