Convert formula to work in diff areas of worksheet

[Robert E. Leonard Sr]

Say your data in column A looks like:

John Smith
123 Maple Ave.
Springfield, NJ 081234
123-456-7890
Jane Doe
100 Center Street
Milton, DE 0123456
234-123-0987

In B1 enter:

=INDIRECT("A" & ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1))

The copy B1 to B1 thru E10 or even further. You will see:

John Smith 123 Maple Ave. Springfield, NJ 081234 123-456-7890
Jane Doe 100 Center Street Milton, DE 0123456
234-123-0987

Each 4 elements have been moved to a row.


i am trying to convert above formula so that it will work if data is in a
different column other than A1.
such as the list starting id D10 an runing to D34

thanks for help
bob leonard
(e-mail address removed)

 

[Pete_UK]

If your data begins in D10 instead, then put this formula in E10:

=INDIRECT("D" & ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1)+9)

Copy across and down as required. Note that I've changed the first A
to D, as that is the column where your data is, and I've added +9 as
your data is 9 rows further down than the original data. Hopefully you
can see how to amend it further if you need to.

Hope ths helps.

Pete

 

[RagDyer]

Non-volatile and perhaps easier to revise:

=INDEX($A$1:$A$100,4*ROWS($1:1)-4+COLUMNS($A:A))

To change the original data location, just revise the range following the
Index*() function - *don't* change anything else!

This formula can be entered *anywhere*, then copied across 4 columns, and
down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
If your data begins in D10 instead, then put this formula in E10:

=INDIRECT("D" & ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1)+9)

Copy across and down as required. Note that I've changed the first A
to D, as that is the column where your data is, and I've added +9 as
your data is 9 rows further down than the original data. Hopefully you
can see how to amend it further if you need to.

Hope ths helps.

Pete