Formula question...I belive row is the answer...but need help

[Guest]

I have text in columns B and C from row 1 to 170. The formula in cell H1 is
=B1 so it displays the text from B1. The formula in cell H2 is =C1 so it
displays the text from C1. I would like to copy this all the way down but it
skips row 2 and pulls the text from cells B3 and C3. I would like to use a
formula in cell H3 to find the text in cell B2 and a formula in cell H4 to
find the text in cell C2. Then I want to be able to copy this down the whole
sheet. Thanks.

 

[Guest]

In H1, enter =INDIRECT("B" & ROUND(ROW()/2,0))
In H2, enter =INDIRECT("C" & ROUND(ROW()/2,0))

Select H1 and H2. Using the fill handle (cursor changes to a plus sign at
the lower right corner of the selected cells), drag down through cell H340.

Hope this helps,

Hutch

 

[Guest]

Here's another way. Similar to my first reply, but only one formula. This one
figures out which column (B or C) to retrieve based on whether the currect
row is even or odd. Enter the following in H1 and copy down through row 340:

=INDIRECT(IF(MOD(ROW(),2),"B","C") & ROUND(ROW()/2,0))

Hope this helps,

Hutch

 

[PCLIVE]

You could use a macro to populate what you want. The following should work.

Sub Macro1()
r = Range("A65536").End(xlUp).Row
h = 1
For i = 1 To r
Range("H" & h).Select
ActiveCell.Formula = "=A" & i
h = h + 1
Range("H" & h).Select
ActiveCell.Formula = "=B" & i
h = h + 1
Next
End Sub


HTH,
Paul

 

[RagDyeR]

Try this non-volatile formula:

=INDEX($B$1:$C$170,ROWS($1:2)/2,MOD(ROWS($1:1)-1,2)+1)

And copy down.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have text in columns B and C from row 1 to 170. The formula in cell H1 is
=B1 so it displays the text from B1. The formula in cell H2 is =C1 so it
displays the text from C1. I would like to copy this all the way down but
it
skips row 2 and pulls the text from cells B3 and C3. I would like to use a
formula in cell H3 to find the text in cell B2 and a formula in cell H4 to
find the text in cell C2. Then I want to be able to copy this down the
whole
sheet. Thanks.

 

[Guest]

thanks, it works perfectly

 

[RagDyer]

You're welcome. and thank you for the feed-back.