Series Question?

  • Thread starter Thread starter D Love
  • Start date Start date
D

D Love

I've been trying to figure this one out and have only come up with
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 fo
however many rows.

Assuming I have too many rows to manually input each cell number, ho
do I get all the data to list in the SAME ROW on a new Sheet2. I
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 sam
column in Sheet2, but if I want to maintain the order, I have to the
start adding and copying extra columns and what not. This secondar
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 t
take data and then try to make it a series and automatically fill i
the remainder of the column, it does not work. The links don't follo
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 th
pattern.

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

Thank
 
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.
 
Excellent! Thanks!

Generally, where can I learn more about toying with this formula?
noticed if I paste it in a cell other than A1 of Sheet2, the result
differ somewhat. I'd like to see how the different parts work so I ca
tinker as needed
 
put it in a cell

with the cell selected, go to the formula bar, select subsets of the formuls
and hit F9 to evaluate that subsection (the subsection must be a section
that can be evaluated). Then hit the escape key to "un-evaluate" that
portion - if you hit enter with it still evaluated, the formula will have
that section replaced with the evaluated result.

This should you allow you to examine it.

also,
You can always put the major parts of it in row 1 and drag them down to see
how they are working.
 
Back
Top