Formular to copy data stored every 9 rows

[VWXXX]

Hello there,

I'm facing the following issue:
I have 2 worksheets and would like to copy the data from worksheet B into worksheet A. 

The problem is, that the relevant data in worksheet B is stored with gaps of 9 rows and i don't know how i can copy this data. Copying by hand is not an option since there are thousands of cells to copy.

It looks like this (x are the respective cells to copy the relevant data from B into):

A:                               

x x x x....              

B:

relevant data

.

.

.

relevant data

.

.

.

relevant data

.....

Is there any way to tell excel to copy the cell of every 9th row (of worksheet B) into adjacent cells in worksheet A? Basically, I need to address and automatically copy cells A1, A10, A19, A28.....and so on from worksheet B into A.

Any help would be very appreciated!

[#2 · Accepted solution]

@VWXXX 

=INDIRECT(ADDRESS(1+(ROW(A1)-ROW($A$1))*9,1,,,"worksheet B"))

You can apply this formula in worksheet A.

[#3]

Thank you so much! That worked! Unfortunately i noticed sometimes the gap is 7 or 8 every now and then. So I fear I still need to edit the formular from time to time. But this definately saves time, thanks again!

[#4]

@VWXXX 

=INDEX('worksheet B'!$A$1:$A$100,SMALL(IF(NOT(ISBLANK('worksheet B'!$A$1:$A$100)),ROW('worksheet B'!$A$1:$A$100)),ROW(A1)))

You are welcome. If you have irregular gaps between the entries you can apply this formula.

[#5]

Thank you, the cells in between the relevant data are not blank unfortunately, so this won't work for me.