Cell referencing


Y

Yuelin

Hi

I have values in a column of cells, A1, A2, A3, A4, A5, A6, A7, A8 and so on.

Then I only want to pick up the value in A2, A4, A6, A8...., and put them
into column B, i.e. B1, B2, B3, B4....

How to do this

Thanks

Yuelin
 
Ad

Advertisements

J

Jacob Skaria

In cell B1 copy the formula and copy that down... this will pick only the
values in A2,A4 ... Once done. copy ColB. Right Click. Paste Special and OK
there itself.

=IF(MOD(ROW(),2)=0,A1,"")


If this post helps click Yes
 
J

Jacob Skaria

In cell B1 copy the formula and copy that down... this will pick only the
values in A2,A4 ... Once done. copy ColB. Right Click. Paste Special and OK
there itself.

=IF(MOD(ROW(),2)=0,A1,"")


If this post helps click Yes
 
Y

Yuelin

Hi Thanks for the reply. It doesn't work properly.


How about in column C, I put in sequential number 1, 2, 3, 4, ....

Then in column B i use the values in C, by:

putting in a function, =$B&($C1*2)

I have this idea, and not sure the format etc. Please can you advise?

Thanks
 
Y

Yuelin

Hi Thanks for the reply. It doesn't work properly.


How about in column C, I put in sequential number 1, 2, 3, 4, ....

Then in column B i use the values in C, by:

putting in a function, =$B&($C1*2)

I have this idea, and not sure the format etc. Please can you advise?

Thanks
 
J

Jacob Skaria

Oops...i didnt notice that you need this values in b1,b2,b3 . Try the below

=INDEX(A:A,ROW()*2)

If this post helps click Yes
 
Ad

Advertisements

J

Jacob Skaria

Oops...i didnt notice that you need this values in b1,b2,b3 . Try the below

=INDEX(A:A,ROW()*2)

If this post helps click Yes
 
Y

Yuelin

Thank you very much

Jacob Skaria said:
Oops...i didnt notice that you need this values in b1,b2,b3 . Try the below

=INDEX(A:A,ROW()*2)

If this post helps click Yes
 
Y

Yuelin

Thank you very much

Jacob Skaria said:
Oops...i didnt notice that you need this values in b1,b2,b3 . Try the below

=INDEX(A:A,ROW()*2)

If this post helps click Yes
 
A

Ashish Mathur

Hi,

You can also try this

Suppose the numbers are in range A2:A9. In cell A1, type Numbers. In cell
A12, use the following formula =MOD(ROW(A2:A9),2)=1. Now go to Data >
Filter > Advanced Filter and select "Copy to another location". In the list
range, refer to range A1:A9. In the criteria rang, refer to range A11A12
and in the copy to box , select D1:D9. Now click on OK.

Please note that this is not a dynamic solution I.e.if any values change in
range A2:A9, the result in D1:D9 will not change - you will have to rerun
the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Ad

Advertisements

A

Ashish Mathur

Hi,

You can also try this

Suppose the numbers are in range A2:A9. In cell A1, type Numbers. In cell
A12, use the following formula =MOD(ROW(A2:A9),2)=1. Now go to Data >
Filter > Advanced Filter and select "Copy to another location". In the list
range, refer to range A1:A9. In the criteria rang, refer to range A11A12
and in the copy to box , select D1:D9. Now click on OK.

Please note that this is not a dynamic solution I.e.if any values change in
range A2:A9, the result in D1:D9 will not change - you will have to rerun
the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Ad

Advertisements


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