# How do I formulate offset function for nth row from a different sheet?

#### skpper

##### New Member
Hi all,

I have Sheet 1, with data starting at Row 17, with a value in every 132rd row (i.e. next cell at Row 149, then 281 etc.) and I need to copy this value into another sheet in consecutive rows by dragging the formula down the rows.

Extra information:
For Sheet 2, the formula starts at Row 16

I'm trying to figure out how to formulate the offset formula to do so, but I'm not too sure what to insert for the ROW referencing:
=OFFSET('Sheet 1'!Q17,ROW()*132,0)

Appreciate any help here.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Zot

##### Well-known Member
The 'Sheet 1'!Q17 is your starting point. The OFFSET is multiple of 137. So, the row offset would be like:
0 * 137
1 * 137
2 * 137

Formula on the other sheet should be
=OFFSET(Sheet5!\$Q\$17,(ROW(Sheet5!Q17)-17)*137,0)

#### Peter_SSs

##### MrExcel MVP, Moderator
I would recommend avoiding the volatile function OFFSET and use a non-volatile approach like this for example.
This formula in row 16 of Sheet2 and copied down. You can edit the 3000 in my formula if your Sheet1 data goes beyond or less than row 3000.

Excel Formula:
``=INDEX(Sheet1!Q\$17:Q\$3000,ROWS(\$16:16)*132-131)``

Zot

#### skpper

##### New Member
Thank you for your help @*Zot and @Peter_SSs!

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Glad we could help. Thanks for the follow-up.

Replies
4
Views
53
Replies
2
Views
543
Replies
15
Views
160
Replies
0
Views
67
Replies
11
Views
557

1,128,122
Messages
5,628,819
Members
416,342
Latest member
BlueDevil12

### 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.

### Which adblocker are you using?

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

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