Excel dragging formula not incrementing properly -

i have excel workbook broken down 2 sheets. on first sheet, of data organized vertically , wanting data show on second sheet horizontally. have been able work using formula.

=sheet1!h2 =sheet1!h12 =sheet1!h22 =sheet1!h32 

however, when select cells , click , drag down copy formulas duplicate ranges don't work. new cells have formula cell range wrong.

=sheet1!h6 =sheet1!h16 =sheet1!h26 =sheet1!h36 

i expecting this.

=sheet1!h42 =sheet1!h52 =sheet1!h62 =sheet1!h72 

i've tried using offset , i'm still having same problem. i'm not sure why it's no incrementing way i'm expecting to, hope can shed light on i'm missing.

to clear bit. have excel file 2 sheets. sheet1 , sheet2. on sheet1, i'm pulling data access query. since of data listed in vertical columns, i'm trying sort data on sheet2 horizontally. i'm linking cells on sheet2 cells on sheet1. data on sheet 1 has variety of parts, numbers, , measurement values recorded.

there 10 entries 1 part type, number, , measurement need gather. on sheet2 need have 1 listing part type, , number, of measurements need captured. example on sheet2 cell f4 needs link sheet1!h2 , cell g4 needs link sheet!h3 , that's 1 row of data. second row on sheet2 have cell f5 link sheet!h12. i'm trying can enter formula correct on few rows of data , highlight , drag cells autofill rest of column. problem that's occurring autofill, increase linked cell count 4 instead of 10.

your question not clear, offset formula should give want. see tried it, lets give try again.

i cannot follow data exactly, formula in d1 , dragged down , across work =offset($a$1,column(d1)-column($d$1),row(d1)-row($d$1))