Cell referencing

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[Yuelin]

Thank you very much

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

 

[Yuelin]

Thank you very much

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

 

[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 D19. 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 D19 will not change - you will have to rerun
the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[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 D19. 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 D19 will not change - you will have to rerun
the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com