Columns
 

Hi,
In Word, it has Columns function that is very useful. When the column A is
full in one page, the data automatically go to column B and then C....
-----
In Excel, I would like to create a sheet, which has 5 columns. I hope that
(on column A) after data is entered into row 60, the next data will move to
column B, not go to the row 61. And then when column B is entered to row 60,
the next data will move to column C....
Please help.
Chi

One play is to mimic this as an output on another sheet ..

Assume data will be entered in A1:A300 in Sheet1

In Sheet2,

Put in A1:

=IF(INDIRECT("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
T("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1)))

Copy A1 across to E1, fill down to E60

A1:E60 will return the desired outputs from the inputs in Sheet1's A1:A300
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Hi,
In Word, it has Columns function that is very useful. When the column A is
full in one page, the data automatically go to column B and then C....
-----
In Excel, I would like to create a sheet, which has 5 columns. I hope that
(on column A) after data is entered into row 60, the next data will move
to
column B, not go to the row 61. And then when column B is entered to row
60,
the next data will move to column C....
Please help.
Chi

Thanks Max, I got it. It is wonderful!

For another case that if data are entered in A1:A300 and B1:B300 (they need
go together)
Ex: A B

Education 2003
Data are entered in A and B columns will stop at row 60 and continue to fill
in D1 and E1 columns and go on....

Please help .
Thanks
Chi


"Max" wrote:

One play is to mimic this as an output on another sheet ..

Assume data will be entered in A1:A300 in Sheet1

In Sheet2,

Put in A1:

=IF(INDIRECT("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
T("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1)))

Copy A1 across to E1, fill down to E60

A1:E60 will return the desired outputs from the inputs in Sheet1's A1:A300
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Hi,
In Word, it has Columns function that is very useful. When the column A is
full in one page, the data automatically go to column B and then C....
-----
In Excel, I would like to create a sheet, which has 5 columns. I hope that
(on column A) after data is entered into row 60, the next data will move
to
column B, not go to the row 61. And then when column B is entered to row
60,
the next data will move to column C....
Please help.
Chi

.. if data are entered in A1:A300 and B1:B300
(they need go together)

Assume the above is made in Sheet1 as before

In Sheet1,

Put in C1: =TRIM(A1&" "&B1)
Copy down to C300

In Sheet2, change the reference to point to Sheet1's col C instead of col A,
i.e.

Put in A1:
=IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1)))

Copy A1 across and fill down to E60 as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Thanks Max, I got it. It is wonderful!

For another case that if data are entered in A1:A300 and B1:B300 Ex:
A B

Education 2003
Data are entered in A and B columns will stop at row 60 and continue to
fill
in D1 and E1 columns and go on....

Please help .
Thanks
Chi

Hi Max,
Again, excellent!

There is a small thing that I need help in this question. Please.
It returns to my designed outputs. Great! However, the data of combined
field (c)appear very close. I would like to make them separately.

Ex: A B
C
Department 1000 then it becomes
Department 1000
Education 200
Education200
Test 5
Test5
I wish they could show like the format below.

Department 1000
Education 200
Test 5

Please help.

Thank you so much!
Chi

"Max" wrote:

.. if data are entered in A1:A300 and B1:B300
(they need go together)

Assume the above is made in Sheet1 as before

In Sheet1,

Put in C1: =TRIM(A1&" "&B1)
Copy down to C300

In Sheet2, change the reference to point to Sheet1's col C instead of col A,
i.e.

Put in A1:
=IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1)))

Copy A1 across and fill down to E60 as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Thanks Max, I got it. It is wonderful!

For another case that if data are entered in A1:A300 and B1:B300 Ex:
A B

Education 2003
Data are entered in A and B columns will stop at row 60 and continue to
fill
in D1 and E1 columns and go on....

Please help .
Thanks
Chi

My last post is mess . Please read this post. Thanks.
Chi
-------------
There is a small thing that I need help in this question. Please.
It returns to my designed outputs. Great! However, the data of combined
field (c)appear very closely. I would like to make them separately.

Ex: A B C

Department 1000 Department1000
Education 200 Education200

I wish they could show like the format below.( Department is at left align and 1000 is at right align)

Department 1000
Education 200
Test 5



Please help.

Thank you so much!
Chi

"Max" wrote:

.. if data are entered in A1:A300 and B1:B300
(they need go together)

Assume the above is made in Sheet1 as before

In Sheet1,

Put in C1: =TRIM(A1&" "&B1)
Copy down to C300

In Sheet2, change the reference to point to Sheet1's col C instead of col A,
i.e.

Put in A1:
=IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1)))

Copy A1 across and fill down to E60 as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Thanks Max, I got it. It is wonderful!

For another case that if data are entered in A1:A300 and B1:B300 Ex:
A B

Education 2003
Data are entered in A and B columns will stop at row 60 and continue to
fill
in D1 and E1 columns and go on....

Please help .
Thanks
Chi

This slight revision might produce an acceptable result ..

In Sheet1,
Put instead in C1: =A1&REPT(" ",10)&B1
Copy down to C300

In Sheet2
Use the same formulas as earlier to fill the range A1:E60
Then just select the cols A to E, and format it to: Align Right

(Adapt the number "10" within REPT to suit if this number is not sufficient.
The "10" means repeat 10 spaces [" "])
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
My last post is mess . Please read this post. Thanks.
Chi
-------------
There is a small thing that I need help in this question. Please.
It returns to my designed outputs. Great! However, the data of combined
field (c)appear very closely. I would like to make them separately.

Ex: A B C

Department 1000 Department1000
Education 200 Education200

I wish they could show like the format below.( Department is at left
align and 1000 is at right align)

Department 1000
Education 200
Test 5



Please help.

Thank you so much!
Chi

In Sheet1,
Put instead in C1: =A1&REPT(" ",10)&B1

And if needed, we could also use in C1:
=TRIM(A1)&REPT(" ",10)&TRIM(B1)

TRIM will remove any extraneous white spaces
present within the cells.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---

Max,

Excellent !
I got all.
Thank you so much.
Chi

"Max" wrote:

In Sheet1,
Put instead in C1: =A1&REPT(" ",10)&B1

And if needed, we could also use in C1:
=TRIM(A1)&REPT(" ",10)&TRIM(B1)

TRIM will remove any extraneous white spaces
present within the cells.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---

You're welcome, Chi ! Glad it helped.
Thanks for the feedback and closure ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Max,

Excellent !
I got all.
Thank you so much.
Chi

Hi Max,
I am sorry that I still need some more help. As you know that my workbook
has two sheets.
Sheet1 which has column A and B, and column C1 =A1&REPT(" ",10)&B1.

Sheet 2, I used this formular
=IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC
so that the value appears from A1 to A60 and then B1 to B60€¦€¦.I like this
layout. However, after I insert new row in sheet 1, the formula in Column C
is not copy down to the new row.

Is there a way to make the formula (in column C) automatically copy down to
the new row?

Sheet1
A B C
DEPARTMENT 55 DEPARTMENT 55
Education Department 555 Education Department 555
Heart Department (new row) Nothing shows here
English and Math 565 English and Math 565

Sheet 2
A
DEPARTMENT 55
Education Department 555
Nothing show here
English and Math 565


The format of the words in sheet 2 will change when I insert new row in
sheet 1.
Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and
Education Department (not bold) will become Education Department(bold)

In all, I still have problems in insert new row in sheet 1 and the format in
sheet 2.
English is my second language, so it is really hard for me to explain the
problems. I hope that it is clear enough for you to understand what I have
been tried to say. Sorry for any confusions.

I don't know why all numbers are messy after I posted.
Thank you
Chi




"Max" wrote:

You're welcome, Chi ! Glad it helped.
Thanks for the feedback and closure ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Max,

Excellent !
I got all.
Thank you so much.
Chi

"Chi" wrote:
....
after I insert new row in sheet 1,
the formula in Column C
is not copy down to the new row ..

Think you need a macro to do this, for which other folks are better
positioned to help you (I don't know, sorry).

Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and
Education Department (not bold) will become Education Department(bold)

Not sure what's happening here, I'm afraid .. but do note that formulas
cannot "carry over" any formatting from the source data.

--
Are you doing all this column re-arrangements
for printing reasons ?

If so, think Jim Cone has a nice Excel add-in "Side by Side"
which could help you.

It's available - free - upon direct request to Jim.

Try this post for details on how to reach Jim: http://tinyurl.com/9dwlv

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max,
Thank you very much for your response. I think that Jim email address is
not correct, but I will try.
Again, Thanks a lot.
Chi Huynh

"Max" wrote:

"Chi" wrote:
....
after I insert new row in sheet 1,
the formula in Column C
is not copy down to the new row ..

Think you need a macro to do this, for which other folks are better
positioned to help you (I don't know, sorry).

Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and
Education Department (not bold) will become Education Department(bold)

Not sure what's happening here, I'm afraid .. but do note that formulas
cannot "carry over" any formatting from the source data.

--
Are you doing all this column re-arrangements
for printing reasons ?

If so, think Jim Cone has a nice Excel add-in "Side by Side"
which could help you.

It's available - free - upon direct request to Jim.

Try this post for details on how to reach Jim: http://tinyurl.com/9dwlv

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I think that Jim email address is
not correct, but I will try.

Sorry about that, didn't notice that google had applied a mask,
but believe the original response by Jim there indicates:

XX

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Max,
Thank you very much for your response. I think that Jim email address is
not correct, but I will try.
Again, Thanks a lot.
Chi Huynh

Thanks! Max
Chi

"Max" wrote:

I think that Jim email address is
not correct, but I will try.

Sorry about that, didn't notice that google had applied a mask,
but believe the original response by Jim there indicates:

XX

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Chi" wrote in message
...
Max,
Thank you very much for your response. I think that Jim email address is
not correct, but I will try.
Again, Thanks a lot.
Chi Huynh

"Chi" wrote:
Thanks! Max
Chi

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--