Apply one formula to multiple cells/Convert rows to columns in EXCEL.

[sunnystone2001]

I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:

In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914


2. Type the following formula in cell C1:

=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

3. Fill this formula across to column F, and then down to row 3.

4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914

This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.

I really appreciate it. Thanks.

[shades]

Quick way: when you select Cells C1:F1, then move cursor to lower right corner of cell F1, it will turn into a crosshair. Then double-click, and it will fill down as far as you have data in Col. B.

[sunnystone2001]

Thanks for your reply, but I can't figure it out. I drag the mouse from c1 to f1 and they are highlighted now. But, when I move cursor to lower corner of cell f1, then double click, nothing happens. When I copy the cormula to it, only c1 get the data, not all cells.

[shades]

Okay, I assumed you had filled the formula from C1 to F1. If you haven't done that, then select C1, and move to lower right corner and when the cursor changes to crosshairs, then click and drag to the right to column F. Then click outside that range. Select cells C1:F1, and follow the process noted above for double-clicking the crosshair.

[sunnystone2001]

Okay, I assumed you had filled the formula from C1 to F1. If you haven't done that, then select C1, and move to lower right corner and when the cursor changes to crosshairs, then click and drag to the right to column F. Then click outside that range. Select cells C1:F1, and follow the process noted above for double-clicking the crosshair.

I got it. Thank you very much.