Pulling data from one column to another column with a formula

  • Thread starter Thread starter jp
  • Start date Start date
J

jp

Say I have the following sheet

A
1 c
2 a
3 b
4 c
5 d
6 a
7 c
8 a
9 b
10 c

How can I create a column in B that displays the values in A1, A3, A5,
A7, A9. Excel will not allow me to just put "=A1" in B1 and "=A3" in
B2 and then drag that pattern down. It does not recognize that
pattern. Help! The answer sheet needs to look like this:
A B
1 c c
2 a b
3 b d
4 c c
5 d b
6 a
7 c
8 a
9 b
10 c
 
In B1 enter =A1

In B2 enter this and copy down.

=OFFSET($A$1,2*ROW()-2,0)


Gord Dibben MS Excel MVP
 
Say I have the following sheet

A
1 c
2 a
3 b
4 c
5 d
6 a
7 c
8 a
9 b
10 c

How can I create a column in B that displays the values in A1, A3, A5,
A7, A9. Excel will not allow me to just put "=A1" in B1 and "=A3" in
B2 and then drag that pattern down. It does not recognize that
pattern. Help! The answer sheet needs to look like this:
A B
1 c c
2 a b
3 b d
4 c c
5 d b
6 a
7 c
8 a
9 b
10 c
Click to expand...

paste this formula in A1 and copy it down:

=INDIRECT("A"&(ROW()-1)*2+1)

if you want to show "" if column A is empty you can do that

=IF(INDIRECT("A"&(ROW()-1)*2+1)="","",INDIRECT("A"&(ROW()-1)*2+1))

hth

Carlo
 
Non-volatile approach:

=INDEX(A:A,2*ROWS($1:1)-1)

Enter anywhere, and copy down as needed.
 
Back
Top