Pulling data from one column to another column with a formula

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
 
G

Gord Dibben

In B1 enter =A1

In B2 enter this and copy down.

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


Gord Dibben MS Excel MVP
 
C

carlo

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

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
 
R

RagDyer

Non-volatile approach:

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

Enter anywhere, and copy down as needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top