PLEASE Need Help Incrementing a Formula

[freefaller6]

Hello Excel Wizards!

I use excel often and usually figure out issues when they come up, but this one has me stumped - would you please help? I have a formula on Sheet 1 in Cell A2 that is simply =Sheet2!B2. On Sheet 2, in column B, I have a long series of values from B2:B186. Back on Sheet 1, I would like A12 to =Sheet2!B3, A22 to =Sheet2!B4, A32 to =Sheet2!B5, etc. I cannot figure out what formula I need, and where to put it on Sheet 1 so that I can click and drag it to copy all the values. Does that make sense? Please help if you can. Thanks a bunch!

[benishiryo]

hi freefaller6, welcome to the forum. try this in A2:
=INDEX(Sheet2!$B$2:$B$186,1+INT((ROWS(A$2:A2)-1)/10))

or maybe to copy down with blanks in between, then:
=IF(MOD(ROWS(A$2:A2)-1,10),"",INDEX(Sheet2!$B$2:$B$186,1+INT((ROWS(A$2:A2)-1)/10)))

[dredwolf]

so, every 10th row, starting from row 2, should go to the next increment of row for sheet2 ? and the in-between rows are same as previous or blank?

Please Login or Register  to view this content.

will give you the first

Please Login or Register  to view this content.

will give you the second

Drag down

Hope this helps

[freefaller6]

Benishiryo and dredwolf - Wizards you are indeed! I guess I don't know as much about Excel as I thought I did. Regardless, your formulas worked and did the trick. I cannot thank you enough. May you both have a great, well-deserved week. Thanks again!