On Sheet2 in Cell A1 put the following formula

=OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/3),MOD(ROW()-1,3))

then drag fill it down the column.

--
Regards,
Tom Ogilvy


D Love wrote in message
...
I've been trying to figure this one out and have only come up with a
workaround, rather than direct solution:

Suppose in Sheet1 I have the following:
A1=First
B1=Middle
C1=Last
A2=George
B2=W
C2=Bush
A3=John
B3=Fitzgerald
C3=Kennedy
..
and so on and so forth, where data is entered across 3 columns for
however many rows.

Assuming I have too many rows to manually input each cell number, how
do I get all the data to list in the SAME ROW on a new Sheet2. In
other words, I am looking for a series or other automatic way to get,
on Sheet2, the following:
a1=First
a2=Middle
a3=Last
a4=George
a5=W
a6=Bush
a7=John
a8=Fitzgerald
a9=Kennedy
.. and so on and so forth.

I could simply copy each column from Sheet1 and past into the same
column in Sheet2, but if I want to maintain the order, I have to then
start adding and copying extra columns and what not. This secondary
method works, but I figured there must be some direct method.

If, in column A of Sheet2, I enter in the first 9 cells from which to
take data and then try to make it a series and automatically fill in
the remainder of the column, it does not work. The links don't follow
the pattern. However, I've noted that if I don't make it a formula,
(i.e., enter "a1" rather than "=a1"), Excel can properly figure out the
pattern.

So I'm at a loss. I'm by no means an Excel expert, so hopefully this
isn't a really dumb question.

Thanks


---
Message posted from http://www.ExcelForum.com/