entering cells in sequence

[garyfahy]

please help!! I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually. please can someone help me. thank you,

1 2
2 4
3 6
4 8
5 10
6 12
7
8
9
10

[hemesh]

try below and drag down
=INDEX(A:A,(ROW($A2)-1)*2)

where A:A represents your column you can replace A:A with your column in which data is present

[garyfahy]

Hi Hemesh,

thanks for the reply. I have tired this and I think I might be doing something wrong because it didn't work. the first cell has this formula - =INDEX(A1:A26;(ROW($A2)-1)*2)
and the following happens;

1 2
2 5
3 8
4 11
5 14
6 17
7 20
8 23
9 26
10 0
11 0
12 0
13 0
14 #REF!
15 #REF!
16 #REF!
17 #REF!
18 #REF!
19 #REF!
20 #REF!
21 #REF!
22 #REF!
23 #REF!
24 #REF!
25 #REF!

[hemesh]

syntax for index is =index(range, row number , column number)
as per of your requirement you need data of each second row means first time it is 2 then it 4 and so on

try below
=INDEX($A$1:$A$26,(ROW($A2)-1)*2) or use =INDEX($A$1:$A$26,(ROW($A1)*2)
row(A1) means 1 then as you go down it becomes Row(A2) means 2 and each time it is multiplied by two producing a series of 2,4,6,8,10,12 and so on

with your version
=INDEX(A1:A26,(ROW($A2)-1)*2) as you drag the formula to 2nd row the formula will become =INDEX(A2:A27,(ROW($A2)-1)*2) and will keep incrementing by as you go down ! means formula is not referring to correct range!

error is coming because at number 14 it will become 14*2 means 28 and for it will be =index($A$1:$A$26,28) but range here is small than the asked row number

[garyfahy]

perfect!! works like a dream. thanks for the explanation. makes sense to me now.

[hemesh]

Thanks for the feedback !
If your query is resolved then mark thread as solved.