DRAGGING A FORMULA WHILE KEEPING THE RANGE OF REFERENCES

[FREDKIMILU]

Hi Lovely all,

I'm trying to drag a formula but would like to skip 36 rows.
I have 2 sheets.
Sheet 1 with results and the other with column to attach values from sheet 1
while skipping 36 rows in sheet1.

For example
I want B4 in sheet 2 to pick the value in H4 in sheet1, B5 to pick H40, B6 to pick H76 and so on
such that when i drag the formula B7 will automatically pick the value in H112 in sheet1 etc


Does anyone know how to do this without having to create Macros?

I really hope you can help me: I am working with a big data and attaching every single cell is tedious
Kindly assist

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDEX(Sheet1!H:H,ROW(A1)*36-32)

 

[FREDKIMILU]

Woow, Fluff. It just worked for me so nicely. Thanks alot.
But for an explanation, the 36-32; I didnt understand why...please

 

[Fluff]

Row(a1) returns 1 so you get 1*36-32 =4 so you get H4
on the next row ROW(A2) returns 2 giving 2*36-32 =40 so you get H40 etc