In cell C1, use:
=OFFSET($B$1,(ROW()-1)*5,0)
Cell D1:
=OFFSET($B$1,(ROW()-1)*5+1,0)
Cell E1:
=OFFSET($B$1,(ROW()-1)*5+2,0)
Cell F1:
=OFFSET($B$1,(ROW()-1)*5+3,0)
Copy the formula down as far as you need to for the 4 columns C to F.

-Simon


"valoo" wrote:


Hi,
I have a list of addresses that need to be in a different format. The
addresses are currently in this format:
Atria Chandler Villas
101 S. Yucca Street
Chandler, Arizona 85244-8419
(blank cell)
Atria Bell Court Gardens
6653 E. Carondelet Drive
Tucson, Arizona 85710-2155
520.886.3600
(blank cell)
Atria Campana del Rio
1550 East River Road
Tucson, Arizona 85718-5800
520.299.1941

I need them in rows instead of columns, like this:
Atria Chandler Villas 101 S. Yucca Street Chandler, Arizona
85244-8419 Chandler, Arizona 85244-8419

The pattern is as follows for the names:
=B1
=B6
=B11
The names are by 5 but excel AutoFill does not recognize this pattern
to continue by adding 5 to the cell number.

Does anyone know a way to get around this? (fill series doesnt work
with a series of cell names)

Thanks.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073