Dragging down a formula which increments every second row

[Treacy_S]

Hi,
I want the following pattern to continue for the length of the worksheet but it doesn't follow the correct pattern when I drag down. This is what I want the pattern to be:
Sheet 2 Z1 = 'Sheet 1'!AD2
Sheet 2 Z2 = 'Sheet 1'!AK2
Sheet 2 Z3 = 'Sheet 1'!AD3
Sheet 2 Z4 = 'Sheet 1'!AK3
Sheet 2 Z5 = 'Sheet 1'!AD4
Sheet 2 Z6 = 'Sheet 1'!AK4
Sheet 2 Z7 = 'Sheet 1'!AD5
Sheet 2 Z8 = 'Sheet 1'!AK5
and so on...

But when I drag down it skips a row each time. For example if I enter the first 2 rows above and drag those down it gives the following results:
Sheet 2 Z1 = 'Sheet 1'!AD2
Sheet 2 Z2 = 'Sheet 1'!AK2
Sheet 2 Z3 = 'Sheet 1'!AD4
Sheet 2 Z4 = 'Sheet 1'!AK4

I'm wondering if there's a formula i can enter in cell Z1 and drag down that would work for me?
Thanks in advance.

 

[steve the fish]

Hi. Heres a way:

=INDEX(Sheet1!$AD:$AK,INT(ROWS($A$1:A4)/2),IF(MOD(ROWS($A$1:A1),2),1,8))

 

[Treacy_S]

Absolutely brilliant I spent hours trying to figure this out thank you!!