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

skpper

New Member
Joined
Dec 7, 2020
Messages
12
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
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
Upvote 0
Solution
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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