How to copy cells horizontally to cells out of sequence

B

brantty

I have data in cells that are on Sheet1. This data is in Cells B2, B3, B4,
B5 etc (All Horizonally, ie staying in same row). I'm working on Sheet2 and
would like to know how to take this data from Sheet1 and copy it to different
cells on Sheet2, but have the data fall vertically. I can copy this once and
repeat, but I am looking for a formula that will do this for me so I can copy
it multiple times.

Example:

Sheet1, B2, will go to Sheet2, K4.
Sheet1 C2, will go to Sheet2, K5
Sheet1 D2, will go to Sheet2, K6

Please help.
 
R

Ragdyer

One way:
Enter this in K4 of Sheet2, and copy down as needed:

=INDEX(Sheet1!$2:$2,ROWS($2:3))
 
R

Roger Govier

Hi

Since you say your source date is on the same row, I guess you mean
B2:E2 as opposed to B2:B5
Copy cells B2:E2
Move to Sheet2, K4 Paste Special>Transpose

Alternatively, you could enter the following formula in Sheet2 cell K4
=OFFSET(Sheet5!$B$2,COLUMN()-11,ROW()-4)
Copy down column K as far as you wish, then across the sheet through
columns L onward, if you want to pick up data from B3:E3 etc.

Regards

Roger Govier
 
Top