I have a text file with a single column of contact / address info with each
address containing 7 rows of data. (sheet1) I want to transpose the rows to
columns (sheet2) so that I can map the data into Outlook.

The following array gives me the results that I want for the second address
by offsetting 7 rows from the previous address.

However, for all the subsequent 800 addresses, I need the formula to
increment the reference points of the offset formulas by 7 more rows. If I
paste this formula in the next row the resulting reference points generated
by excel are A9 and A15. I need the new reference points to be A15 and A21.

Is there a way to force the reference points to increment by 7?
Is there an easier way to do this?

{=TRANSPOSE(OFFSET(Lincolntext4!A8,7,0):OFFSET(Lin colntext4!A14,7,0))}

here is what the first two addresses look like in the source (sheet1) Rows
1-14

Miller Sherrill Blake Eagle CPA PA
Post Office Box 782 236 East Main Street
Lincolnton, NC 28093
Phone: 704-732-2234
Fax: 704-732-6041

www.msbcpa.com
Donna J. Scates, CPA, PA
2817 Highway 27 East
Lincolnton, NC 28092
Phone: 704-735-8786
Fax: 704-735-1584

www.mycpa.com

Thanks!
--
excelhel_p

Hi,

Well, I don't know if you consider this easier, but:

=OFFSET(Sheet2!$A$1,(ROW(A1)-1)*7+COLUMN(A1)-1,)

Where Sheet2!A1 is where the first item of my test data is, you need to
adjust that for your data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"excelhel_p" wrote:

I have a text file with a single column of contact / address info with each
address containing 7 rows of data. (sheet1) I want to transpose the rows to
columns (sheet2) so that I can map the data into Outlook.

The following array gives me the results that I want for the second address
by offsetting 7 rows from the previous address.

However, for all the subsequent 800 addresses, I need the formula to
increment the reference points of the offset formulas by 7 more rows. If I
paste this formula in the next row the resulting reference points generated
by excel are A9 and A15. I need the new reference points to be A15 and A21.

Is there a way to force the reference points to increment by 7?
Is there an easier way to do this?

{=TRANSPOSE(OFFSET(Lincolntext4!A8,7,0):OFFSET(Lin colntext4!A14,7,0))}

here is what the first two addresses look like in the source (sheet1) Rows
1-14

Miller Sherrill Blake Eagle CPA PA
Post Office Box 782 236 East Main Street
Lincolnton, NC 28093
Phone: 704-732-2234
Fax: 704-732-6041

www.msbcpa.com
Donna J. Scates, CPA, PA
2817 Highway 27 East
Lincolnton, NC 28092
Phone: 704-735-8786
Fax: 704-735-1584

www.mycpa.com

Thanks!
--
excelhel_p

Try this on sheet 2

in A1: =OFFSET(Sheet1!$A$1,(ROW(A1)-1+COLUMN(A1)-1)*7,0)
in B1: =OFFSET(Sheet1!$A$1,(ROW(A1)-1)*7+COLUMN(A1),0)
fill the formula in B1 on over to G1. Those should pick up all the
information for the 1st address.
Now fill the formulas in A1:G1 on down the sheet until you've picked up all
of the information from Sheet1

"excelhel_p" wrote:

I have a text file with a single column of contact / address info with each
address containing 7 rows of data. (sheet1) I want to transpose the rows to
columns (sheet2) so that I can map the data into Outlook.

The following array gives me the results that I want for the second address
by offsetting 7 rows from the previous address.

However, for all the subsequent 800 addresses, I need the formula to
increment the reference points of the offset formulas by 7 more rows. If I
paste this formula in the next row the resulting reference points generated
by excel are A9 and A15. I need the new reference points to be A15 and A21.

Is there a way to force the reference points to increment by 7?
Is there an easier way to do this?

{=TRANSPOSE(OFFSET(Lincolntext4!A8,7,0):OFFSET(Lin colntext4!A14,7,0))}

here is what the first two addresses look like in the source (sheet1) Rows
1-14

Miller Sherrill Blake Eagle CPA PA
Post Office Box 782 236 East Main Street
Lincolnton, NC 28093
Phone: 704-732-2234
Fax: 704-732-6041

www.msbcpa.com
Donna J. Scates, CPA, PA
2817 Highway 27 East
Lincolnton, NC 28092
Phone: 704-735-8786
Fax: 704-735-1584

www.mycpa.com

Thanks!
--
excelhel_p

Another way is to replace the reference Lincolntext4!A8 with

INDIRECT("Lincolntext4!A" & (ROW()-ROW($A$1))*7+1)

in Row2 the above will refer cell 8
in Row3 the above will refer cell 15 etc;

You need to adjust this to suit...........

If this post helps click Yes
---------------
Jacob Skaria


"excelhel_p" wrote:

I have a text file with a single column of contact / address info with each
address containing 7 rows of data. (sheet1) I want to transpose the rows to
columns (sheet2) so that I can map the data into Outlook.

The following array gives me the results that I want for the second address
by offsetting 7 rows from the previous address.

However, for all the subsequent 800 addresses, I need the formula to
increment the reference points of the offset formulas by 7 more rows. If I
paste this formula in the next row the resulting reference points generated
by excel are A9 and A15. I need the new reference points to be A15 and A21.

Is there a way to force the reference points to increment by 7?
Is there an easier way to do this?

{=TRANSPOSE(OFFSET(Lincolntext4!A8,7,0):OFFSET(Lin colntext4!A14,7,0))}

here is what the first two addresses look like in the source (sheet1) Rows
1-14

Miller Sherrill Blake Eagle CPA PA
Post Office Box 782 236 East Main Street
Lincolnton, NC 28093
Phone: 704-732-2234
Fax: 704-732-6041

www.msbcpa.com
Donna J. Scates, CPA, PA
2817 Highway 27 East
Lincolnton, NC 28092
Phone: 704-735-8786
Fax: 704-735-1584

www.mycpa.com

Thanks!
--
excelhel_p

How about using INDEX() a single formula for columns and rows...

=INDEX($A:$A,(ROW(A1)-1)*7+1+COLUMN(A1)-1)

With your data in ColA starting from row1 copy the above formula in B1 and
copy that upto H1..Drag below as required..


If this post helps click Yes
---------------
Jacob Skaria


"excelhel_p" wrote:

I have a text file with a single column of contact / address info with each
address containing 7 rows of data. (sheet1) I want to transpose the rows to
columns (sheet2) so that I can map the data into Outlook.

The following array gives me the results that I want for the second address
by offsetting 7 rows from the previous address.

However, for all the subsequent 800 addresses, I need the formula to
increment the reference points of the offset formulas by 7 more rows. If I
paste this formula in the next row the resulting reference points generated
by excel are A9 and A15. I need the new reference points to be A15 and A21.

Is there a way to force the reference points to increment by 7?
Is there an easier way to do this?

{=TRANSPOSE(OFFSET(Lincolntext4!A8,7,0):OFFSET(Lin colntext4!A14,7,0))}

here is what the first two addresses look like in the source (sheet1) Rows
1-14

Miller Sherrill Blake Eagle CPA PA
Post Office Box 782 236 East Main Street
Lincolnton, NC 28093
Phone: 704-732-2234
Fax: 704-732-6041

www.msbcpa.com
Donna J. Scates, CPA, PA
2817 Highway 27 East
Lincolnton, NC 28092
Phone: 704-735-8786
Fax: 704-735-1584

www.mycpa.com

Thanks!
--
excelhel_p