Using Fill to copy from other sheets

[Rogerxwilliams]

I am copying data from one Excel sheet to another. In the source sheet the
data I want to copy does not reside in sequential lines. Instead it occurs on
every 6th line i.e. X5, X11, X17 etc... I want to populate a column in the
destination sheet solely with data from every 6th line - so I have used "="
and then clicked the source cell on the other sheet three times to get a
"sequence" started. I then select all three cells on the destination sheet
and drag this down to try to create a full column following the same
sequence... this is where something strange happens... the source cell
numbers "reset" depending on the address of the starting cell - I am faced
with a hugely laborious task copying over hundreds of numbers - any ideas???

 

[Mike H]

Hi,

Try this

=INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5)

drag down as required

 

[Rogerxwilliams]

not understanding this and cant get it to work

Hi,

Try this

=INDEX(Sheet2!X:X,(ROW(A1)-1)*6+5)

drag down as required

 

[Mike H]

Hi,

Put the formula anywhere except column X of sheet 2 and it will return the
contents of Sheet 2 - X5.

Drag down for Sheet 2 - X11 etc.

Mike

 

[Rogerxwilliams]

Hi Mike
Possible misunderstanding.
My source data is in sheet 1 in cells X5 (then) X11 (then) X17 etc through
to "hundreds" of entries.
I am trying to copy these cells "only" into a sequential list on another
sheet (e.g. C1, C2, C3 etc... so that I can total them up) Using your
approach I am still getting strnage combinations / sequences of data.

Appreciate the help

 

[Mike H]

Hi

Possible misunderstanding.
My source data is in sheet 1 in cells X5 (then) X11 (then) X17 etc through
to "hundreds" of entries.

So the formula in C1 and any sheet becomes

=INDEX(Sheet1!X:X,(ROW(A1)-1)*6+5)

it will return the contents of X5 on sheet 1. Drag down into c2 and it will
return X11 of sheet 1

Mike

 

[Mike H]

On reflection perhaps it will become clear if I explain how the formula works

=INDEX(Sheet1!X:X,(ROW(A1)-1)*6+5)

take a simple example of the index formula

=INDEX(Sheet1!X:X,3)

this simply returns the value contained in X3 of sheet 1.

So to get the stepping you require we need a bit of ingenuity. First we want
to get a 5 for the row so this bit of the formula does that

=...................ROW(A1)-1)*6+5)

R0W(a1) first evaluates as 1 so we get the sum

1-1*6+5= 5

so we get

=INDEX(Sheet2!X:X,5)

drag down 1 row and the formula increments to

=...................ROW(A2)-1)*6+5)

2-1*6+5=11

so now our formula becomes

=INDEX(Sheet2!X:X,11)

etc.

Mike

 

[Mike H]

Glad I could help

God knows how!

Look at my explanation in the previous post

Mike

 

[Rogerxwilliams]

Oh... wait - I've got it now fab - works a treat - God knows how!
You are a Genius