copying incremental rows over columns

[aia8998]

hello all, i have researched for a day and can't come up with the right formula so any help will be greatly appreciated.

I have data on a spreadsheet listed in rows (name of spreadsheet is Cleared by Returns Data). The information I need is in every 10th cell of the H column.

On a different spreadsheet named "Summary" , in cell L3 I would like a formula that will display the value of cell H2 on the Cleared by Returns Data spreadsheet(the data starts at L3 due to headers) AND when I drag the formula to the right, I would like the formula to increase the cell reference by 10. Meaning cell M3 (of Summary spreadsheet) will now display cell H12 of the Cleared by Returns Data spreadsheet, cell N3 will now display cell H22, and so on.

I have been playing around with the INDEX, INDIRECT, ADDRESS, and OFFSET but just can't seem to come up with the right combination that works when I drag it to the right.

 

[guerillaunit]

Hi aia, use this

Sub test()
Dim lastrowofdata As Long
Dim summarystartrow As Long
summarystartrow = 12
lastrowofdata = ActiveSheet.UsedRange.Rows.Count
Sheets("Cleared By Returns Data").Activate
For i = 2 To lastrowofdata Step 10
    Range("H" & i).Copy
    Sheets("Summary").Activate
    Cells(3, summarystartrow).PasteSpecial
    summarystartrow = summarystartrow + 1
    Sheets("Cleared By Returns Data").Activate
Next
End Sub