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

skpper

New Member
Joined
Dec 7, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Nov 26, 2020
Messages
1,055
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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)
 
  • Like
Reactions: Zot
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top