copy a cell reference down starting at row x and then every nth row

Morphies

New Member
Joined
Apr 28, 2016
Messages
19
Hi All,

I'm trying to tidy up some data into another tab.

The source data is infinitely lings I wanted to create a formula to tidy the data into a new sheet..

The data starts on row 5 every time and thenext set of data is every 10 rows thereafter.

I’m guessing I need to use the offset, but cantget it to work…


Rich (BB code):
Rich (BB code):
 =OFFSET(Sheet1!$J$5,(ROW()-1)*10,0)


This gives me the reference in J15 but I needit to start at J5 then go down to J15, J25, J30 etc

Any clues?

Thanks,


 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
My guess is that you are putting this first formula on row 2.
If so, subtract 2, not 1, from ROW(), i.e.
Code:
[FONT=Arial]=OFFSET(Sheet1!$J$5,(ROW()-[COLOR=#ff0000][B]2[/B][/COLOR])*10,0)[/FONT]
 
Upvote 0
You are welcome.
Basically, you want to use the row number of the first row you are placing this formula in.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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